Query Rewrite. How to rewrite the SQL Query to avoid multiple Nested Loops - Left Outer Join

link textHi All,

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.

avatar image By DDKG 0 asked May 02, 2014 at 06:20 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

2 answers: sort voted first

Here is a slightly more concise version of the query - most importantly it only joins against each table once. Also I moved the DATE_INFO join to a simple variable - I do not see any need to involve the DATE_INFO table at all. What other answer could it provide, other than today's date at midnight?

 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.

avatar image By Aaron Bertrand ♦ 1.7k answered May 02, 2014 at 08:58 PM
more ▼
(comments are locked)
avatar image DDKG May 06, 2014 at 12:30 AM

Hi Aaron,

The query had reduced the JOINS, but for some of the data is showing in-correctly.

For example, for a item, there is no MMS_TYPE, but still it show MMS_TYPE_TEXT in the column.

I am attaching the old query, new query, the table, the result of the old query and new query.

Appreciate your help.

avatar image DDKG May 06, 2014 at 01:30 AM

Hi Aaron,

The query you provided me is for 1 date. The query i uploaded now is for all the dates, hence the ITEM_VERSION join in the Query.

10|10000 characters needed characters left

Lots of key lookups in there, so it looks like there's an opportunity to either add indexes or add columns to existing indexes. Also, I see table2.index4 called twice in the execution plan. So it might be possible to re-arrange some of the code so that operation is only needed once.

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,

-Kev

avatar image By Kevin 141 answered May 02, 2014 at 06:54 PM
more ▼
(comments are locked)
avatar image DDKG May 02, 2014 at 07:13 PM

Hi Kevin,

The above query is the real code. I couldnt attach the execution plan to this thread.

avatar image DDKG May 02, 2014 at 07:13 PM

I have attached the actual execution plan to my 1st thread.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x2
x1

asked: May 02, 2014 at 06:20 PM

Seen: 281 times

Last Updated: May 06, 2014 at 01:30 AM