CODEWITHSUNDEEP
sqlsql-server
The_Longster
The_Longster

Reputation: 9

SQL Query Performance Issues Using Subquery

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?

Upvotes: 0

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 3

Related Questions