Query Rewrite. How to rewrite the SQL Query to avoid multiple Nested Loops – Left Outer Join
Need help to re-write the SQL Query. Due to numerous NL Joins, the sql query is running slow by slow, as the data in the table increases.
DECLARE @d DATETIME = CONVERT(DATE, GETDATE()); SELECT i.ITEM_ID, i.ITEM_TYPE, i.START_DATETIME, i.END_DATETIME, F_NAME = MAX(CASE ip.PROPERTY_TYPE WHEN 'NAME' THEN ip.PROPERTY_STRING END), F_STATE = MAX(CASE ip.PROPERTY_TYPE WHEN 'STATE' THEN ip.PROPERTY_STRING END), S_TEXT = MAX(CASE cl.LIST_TYPE WHEN 'STATE' THEN cl.CODE_TEXT END), F_COUNTY = MAX(CASE ip.PROPERTY_TYPE WHEN 'COUNTY' THEN ip.PROPERTY_STRING END), C_TEXT = MAX(CASE cl.LIST_TYPE WHEN 'COUNTY' THEN cl.CODE_TEXT END) FROM dbo.ITEM AS i LEFT OUTER JOIN dbo.ITEM_PROPERTY AS ip ON i.ITEM_ID = ip.ITEM_ID AND ip.PROPERTY_TYPE IN ('NAME','STATE','COUNTY') AND ip.START_DATETIME <= @d AND ip.END_DATETIME > @d LEFT OUTER JOIN dbo.CODE_LIST AS cl ON ip.PROPERTY_STRING = cl.CODE AND cl.CULTURE = 'en-US' AND cl.LIST_TYPE IN ('STATE','COUNTY') WHERE i.ITEM_TYPE = 'FIELD' AND i.START_DATETIME <= @d AND i.END_DATETIME > @d GROUP BY i.ITEM_ID, i.ITEM_TYPE, i.START_DATETIME, i.END_DATETIME;
Now, that should absolutely lead to a more efficient query plan, but to see a difference in performance, you'd have to take a closer look at where your current indexes are falling short, and/or ensure statistics are up to date. I haven't looked at the actual plan, but you should probably re-generate a new plan based on this more efficient query anyway.
However, it's really hard to tell much about the query without 1) any of the real code, 2) an actual execution plan. If you can provide those, it'd help the analysis quite a lot.
Hope this helps,