SQL Server group by foreign key and select dependant columns

Question

I have some performance issues when querying in SQL server. I need to GROUP BY foreign key (academic_unit_id), but I also need to select a column that is dependant on the FK (academic_unit_name). In SQL Server I can't just select academic_unit_name in the same query, it must be aggregated or in the GROUP BY. I think the options I have are:

SELECT academic_unit_id (foreign key) and academic_unit_unit_name (dependant on FK), and then group by both

SELECT 
    ria.COD_DOCENTE_SCD,
    ria.COD_CURSO_SECCION_SCD,
    ria.COD_ITEM_SCD,
    ria.COD_GRUPO_PREGUNTA_SCD,
    alumno.IDN_UNIDAD_ACADEM_SCD, -- id
    alumno.NOM_UNIDAD_ACADEM_SCD, -- name
    ROUND(COUNT(case when opcion.punto = 1 then 1 end), 2) as amount_yes,
    ROUND(COUNT(case when opcion.punto = 0 then 1 end), 2) as amount_no
FROM BANNER_ENCUESTA.R_RESP_ITEM_ALUMNO_CSP_SCD AS ria INNER JOIN
    BANNER_ENCUESTA.TIPO_OPCION_SCD AS opcion ON ria.COD_TIPO_OPCION_SCD = opcion.COD_TIPO_OPCION_SCD INNER JOIN
    BANNER_ENCUESTA.ALUMNO_SCD AS alumno ON ria.COD_ALUMNO_SCD = alumno.COD_ALUMNO_SCD
GROUP BY 
    ria.COD_DOCENTE_SCD,
    ria.COD_CURSO_SECCION_SCD,
    ria.COD_ITEM_SCD,
    ria.COD_GRUPO_PREGUNTA_SCD,
    alumno.IDN_UNIDAD_ACADEM_SCD, -- group by FK
    alumno.NOM_UNIDAD_ACADEM_SCD -- group by name

GROUP BY PK and aggregate the academic_unit_name. I can aggregate using max, since all names are equals for a given id.

SELECT 
    ria.COD_DOCENTE_SCD,
    ria.COD_CURSO_SECCION_SCD,
    ria.COD_ITEM_SCD,
    ria.COD_GRUPO_PREGUNTA_SCD,
    alumno.IDN_UNIDAD_ACADEM_SCD, -- id
    MAX(alumno.NOM_UNIDAD_ACADEM_SCD), -- aggregate name
    ROUND(COUNT(case when opcion.punto = 1 then 1 end), 2) as amount_yes,
    ROUND(COUNT(case when opcion.punto = 0 then 1 end), 2) as amount_no
FROM BANNER_ENCUESTA.R_RESP_ITEM_ALUMNO_CSP_SCD AS ria INNER JOIN
    BANNER_ENCUESTA.TIPO_OPCION_SCD AS opcion ON ria.COD_TIPO_OPCION_SCD = opcion.COD_TIPO_OPCION_SCD INNER JOIN
    BANNER_ENCUESTA.ALUMNO_SCD AS alumno ON ria.COD_ALUMNO_SCD = alumno.COD_ALUMNO_SCD
GROUP BY 
    ria.COD_DOCENTE_SCD,
    ria.COD_CURSO_SECCION_SCD,
    ria.COD_ITEM_SCD,
    ria.COD_GRUPO_PREGUNTA_SCD,
    alumno.IDN_UNIDAD_ACADEM_SCD --Group by FK

SELECT only academic_unit_id and then JOIN with AcademicUnit again to obtain the name.

with banner_questions as (
SELECT 
    ria.COD_DOCENTE_SCD,
    ria.COD_CURSO_SECCION_SCD,
    ria.COD_ITEM_SCD,
    ria.COD_GRUPO_PREGUNTA_SCD,
    alumno.IDN_UNIDAD_ACADEM_SCD -- id,
    ROUND(COUNT(case when opcion.punto = 1 then 1 end), 2) as amount_yes,
    ROUND(COUNT(case when opcion.punto = 0 then 1 end), 2) as amount_no
FROM BANNER_ENCUESTA.R_RESP_ITEM_ALUMNO_CSP_SCD AS ria INNER JOIN
    BANNER_ENCUESTA.TIPO_OPCION_SCD AS opcion ON ria.COD_TIPO_OPCION_SCD = opcion.COD_TIPO_OPCION_SCD INNER JOIN
    BANNER_ENCUESTA.ALUMNO_SCD AS alumno ON ria.COD_ALUMNO_SCD = alumno.COD_ALUMNO_SCD
GROUP BY 
    ria.COD_DOCENTE_SCD,
    ria.COD_CURSO_SECCION_SCD,
    ria.COD_ITEM_SCD,
    ria.COD_GRUPO_PREGUNTA_SCD,
    alumno.IDN_UNIDAD_ACADEM_SCD) -- group by FK
SELECT
    banner_questions.*,
    student_ua.name -- Join with name
from NORMALIZADO_PRELIMINAR.AcademicUnit as student_ua INNER JOIN
    banner_questions on student_ua.id = banner_questions.IDN_UNIDAD_ACADEM_SCD

In terms of performance, I'd like to know if one of these alternatives is better and under what assummptions. Also, I'd like to know if there are better choices to get the same result.

user_14267425 · Answer

In the question I think you mean Foreign key rather than Primary key... the field is a Primary key in another table Academic_unit but is looking at, say, student_unit records which have an FK to Academic_unit.

So the question is for the field alumno.NOM_UNIDAD_ACADEM_SCD - do you GROUP BY it, MAX() it or JOIN it later?

Personally I suggest just

trying all three and see which ones run the fastest - which is best really depends on specific circumstances - and they often run very similarly
use the simplest version if they run at similar speeds - which is likely to be the GROUP BY version

In particular, the GROUP BY and MAX() should result in almost identical plans as they are sorted the same way.

The 'join it later' approach can have some speed advantages in certain circumstances (particularly when it's not just being joined to a reference table, but to a broader set of sub-queries), but I'm often wary about these. They have the disadvantage of making your code a bit more complex - which can have issues if you use the data for other things, or if SQL Server has bad estimates for the amount of data it expects. In this case, as this is just linking to the reference table alumno, it's unlikely to give any specific advantage.

In your code for option 3 above, you still have links to BANNER_ENCUESTA.ALUMNO_SCD AS alumno. The advantage of doing the join later would be to remove that from the initial grouping component, then link to it later to get the specific values e.g.,

In the GROUP BY within the CTE, also group by ria.COD_ALUMNO_SCD, but remove BANNER_ENCUESTA.ALUMNO_SCD AS alumno from the FROM clause
Put BANNER_ENCUESTA.ALUMNO_SCD AS alumno into the main SELECT part of the query, and join to banner_questions on that field

Note there is also a fourth option (temporary tables) which is used when

SQL Server gets estimates for how many rows it expects really wrong - and makes a really bad plan
You're joining not to reference tables, but to views (particularly if they have 'TOP' expressions or 'GROUP BY' in them) - in these cases, SQL Server may sometimes run the view completely once for every row in the join.

In these cases, it can be useful to split the query into two parts along the lines of #3, but instead of a CTE, you save it into a temporary table e.g., SELECT .... INTO #temp FROM ... GROUP BY.

You then use the temporary table, joined to the view that was problematic, and it will often run better.

SQL Server group by foreign key and select dependant columns

1 Answers