Query Rewrite

Hi,

Is there a better way to rewrite the SQL Query ? Please, suggest.

avatar image By DDKG 0 asked May 14, 2014 at 02:59 PM
more ▼
(comments are locked)
avatar image SQLkiwi ♦ May 14, 2014 at 04:47 PM

Please explain the problem you are experiencing, and what help you require. "Is there a better way to rewrite" is not enough to go on. The full text of the query, table and index definitions, a post-execution plan collected by Plan Explorer, and a description of your database design model (EAV?) would help a lot.

10|10000 characters needed characters left

2 answers: sort oldest

Based only on that estimated plan, an index on the item_link table on start_datetime or end_datetime depending on how your data skews would be very helpful. That table is scanned four times in this statement on the same index for roughly the same predicate (start_datetime and end_datetime).

In fact the warning on the first node shows that the missing index would be helpful.

 CREATE NONCLUSTERED INDEX
     [<Name of Missing Index, sysname,>]
 ON [dbo].[ITEM_LINK]
 (
     [START_DATETIME],
     [END_DATETIME],
     [FROM_ITEM_TYPE]
 )
 INCLUDE
 (
     [FROM_ITEM_ID],
     [TO_ITEM_ID]
 );
avatar image By Dan Holmes 725 answered May 14, 2014 at 03:06 PM
more ▼
(comments are locked)
avatar image DDKG May 14, 2014 at 03:09 PM

Hi Dan,

I am looking more towards, how to avoid the "Nested Loop Inner Joins". i.e. Rewriting the Code using CTE, etc. etc.

Can you give me some suggestions ?

avatar image SQLkiwi ♦ May 14, 2014 at 04:51 PM

@DDKG The nested loops joins are not your problem (and you probably need more of them, not fewer). Providing proper indexes to support your queries is a basic performance tuning necessity. CTEs are nothing more than in-line views, they might make your code clearer, but they won't help performance. Clarify your question please.

10|10000 characters needed characters left

This is coded very strangely. There's no reason at all (that I can think of) that you would reference the same tables in the FROM clause like this:

FROM ITEM_PROPERTY c, ITEM_PROPERTY r, ITEM_PROPERTY d, ITEM_PROPERTY s, ITEM_PROPERTY f, ITEM_PROPERTY rt, ITEM_PROPERTY st, ITEM_PROPERTY i, ITEM_LINK rc, ITEM_LINK dr, ITEM_LINK sd, ITEM_LINK fs, ITEM_LINK rs, ITEM_LINK sr, ITEM_LINK ist

Why not reference each table you need only once and then modify the WHERE clause conditions as appropriate?

avatar image By Kevin 141 answered May 14, 2014 at 03:36 PM
more ▼
(comments are locked)
avatar image DDKG May 14, 2014 at 03:42 PM

Hi Kevin,

Can you just give me a sample query to start with ?

avatar image SQLkiwi ♦ May 14, 2014 at 04:40 PM

Looks like something EAV to me. Yikes.

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.