SQL Query Performance Issues Using Subquery

Question

I am having issues with my query run time. I want the query to automatically pull the max id for a column because the table is indexed off of that column. If i punch in the number manually, it runs in seconds, but i want the query to be more dynamic if possible.

I've tried placing the sub-query in different places with no luck

SELECT *
FROM TABLE A
JOIN TABLE B
    ON A.SLD_MENU_ITM_ID = B.SLD_MENU_ITM_ID
    AND B.ACTV_FLG = 1
WHERE A.WK_END_THU_ID_NU >= (SELECT DISTINCT MAX (WK_END_THU_ID_NU) FROM TABLE A)
AND A.WK_END_THU_END_YR_NU = YEAR(GETDATE())
AND A.LGCY_NATL_STR_NU IN (7731)
AND B.SLD_MENU_ITM_ID = 4314

I just want this to run faster. Maybe there is a different approach i should be taking?

DISTINCT MAX()? An aggregate query with no GROUP BY will only return one 1 row; why do you need DISTINCT? — Thom A
– Thom A ♦, Commented Aug 16, 2019 at 15:57

Gordon Linoff · Accepted Answer · 2019-08-16 15:58:58Z

I would move the subquery to the FROM clause and change the WHERE clause to only refer to A:

SELECT *
FROM A CROSS JOIN
     (SELECT  MAX(WK_END_THU_ID_NU) as max_wet
      FROM A
     ) am
     ON a.WK_END_THU_ID_NU = max_wet JOIN
     B
     ON A.SLD_MENU_ITM_ID = B.SLD_MENU_ITM_ID AND
        B.ACTV_FLG = 1
WHERE A.WK_END_THU_END_YR_NU = YEAR(GETDATE()) AND
      A.LGCY_NATL_STR_NU IN (7731) AND
      A.SLD_MENU_ITM_ID = 4314;  -- is the same as B

Then you want indexes. I'm pretty sure you want indexes on:

A(SLD_MENU_ITM_ID, WK_END_THU_END_YR_NU, LGCY_NATL_STR_NU, SLD_MENU_ITM_ID)
B(SLD_MENU_ITM_ID, ACTV_FLG)

I will note that moving the subquery to the FROM clause probably does not affect performance, because SQL Server is smart enough to only execute it once. However, I prefer table references in the FROM clause when reasonable. I don't think a window function would actually help in this case.

Collectives™ on Stack Overflow

SQL Query Performance Issues Using Subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related