Weird query behaviour. 1

[mmilan]

I have a table called Product, which is involved in an SQL query for producing details of parts that need adding to stocksheets during a stocktake. The query basically takes the product table, and does a number of LEFT JOINS out to various other tables...

Now, product contains a couple of fields (that aren't used in the joins) called Pg(ie. Product group) and Range, and these fields appear in a where clause at the end of this sorry mess - allowing the user to just produce stock sheets for parts in a particular product group and a particular product range.

The problem I am having is that for a particular combination of group and range, the query is taking ages to come back. If however I choose say a different group, with more parts in it, the query comes back almost instantly...

My question is - does anyone have an idea what the hell is going on?

SQL is as follows

SELECT P.Keycode, S.Locn, '' As Batch, S.[Free], '' SortKey1, '' SortKey2 
FROM RRPC.dbo.Product P 
LEFT JOIN RRPC.dbo.Stock S ON P.Keycode = S.Part And 'PENZANCE' = S.Branch 
LEFT JOIN vw_StockSheets SS ON 'PENZANCE' = SS.Branch And P.Keycode = SS.Part 
LEFT JOIN RRPC.dbo.Kits K ON P.Keycode = K.Part  
LEFT JOIN (SELECT TU.Part, Sum(TU.Total) UsgTotal FROM (SELECT Part, IsNull(Jan, 0) + IsNull(Feb, 0) + IsNull(Mar, 0) + 
IsNull(Apr, 0) + IsNull(May, 0) + IsNull(Jun, 0) + IsNull(Jul, 0) + IsNull(Aug, 0) + IsNull(Sep, 0) + IsNull(Oct, 0)+ IsNull(Nov, 0) + 
IsNull(Dec, 0) Total  FROM RRPC.dbo.Mvk 
WHERE Branch = 'PENZANCE' And [Year] = '2004' )
 TU GROUP BY TU.Part ) 
U ON P.Keycode = U.Part  
WHERE (S.Branch = 'PENZANCE' Or S.Branch Is Null) And 
P.Sunder = '' And SS.Part Is Null  And (IsNull(S.[Free], 0) <> 0 Or IsNull(U.UsgTotal, 0) <> 0)  And
 K.Part Is Null  And P.Cind <> 'S' And P.Range = 'QH' And P.Pg = 'CABLES'

I have even introduced a specific index (non-clustered - clustered being used for primary key - ie the part number) covering the group and the range, but this has only had a marginal effect. I've also rebuilt the statistics on the product table.

The Estimated Query Execution Plan seems to hint that the problem is in a Bookmark lookup after it has done a seek on the new index.

All contributions gratefully received...

mmilan.

 

[SQLSister]

My performance tuning book suggests three ways to get rid of a bookmark lookup, a clusteredindex coving all the appropriate fields (usually not practical for othee reasons), a covering index with all the fields included or a series of smaller indexes that together cover all the fields which will then use an index join instead of a bookmark lookup.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[mmilan]

Many thanks for your comments.

I have already tried giving a covering non-clustered index for the fields that seem to be causing the problem, but sitting in here my living room some twenty miles from the office it occurs to me that I haven't covered other fields in the query.

I'll give it try - so thanks once again for your suggestion.

mmilan