Query Rewrite

DDKG 2014-05-14 14:59:40

Hi,

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

/storage/attachments/889-tmp2201.queryanalysis
SQLkiwi 2014-05-14 16:47:05
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.
Dan Holmes 2014-05-14 15:06:49
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]
);
DDKG 2014-05-14 15:09:15
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 ?

SQLkiwi 2014-05-14 16:51:27
@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.
Kevin 2014-05-14 15:36:28
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?

DDKG 2014-05-14 15:42:11
Hi Kevin,

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

SQLkiwi 2014-05-14 16:40:02
Looks like something EAV to me. Yikes.
May 14, 2014
No Comments