SQL Server Performance Forum – Threads Archive

Tuning of a Query…

I HAVE QUERY AS FOLLOWS:<br /><br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote">SELECT DISTINCT s.supplier_code, p.itemcode, s.Item_Code, s.Region_Code, s.Lead_Time<br />FROM PL_Supplier_Product_Relationship S, ProductHierarchy P<br />WHERE s.validconsumed = 1 <br /> AND S.Item_Code=P.ItemCode <br />AND P.TreeID IN (SELECT TreeID FROM ProdTreeName)<hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />I have nonclustered index on s.validconsumed,S.Item_Code, P.ItemCode, P.TreeID columns separately. I have "TreeID FROM ProdTreeName" PK (cluster index) on TreeID column. I have update the statistice and rebuild the indexes (in ordre o make sure query engine using fresh information). But still give hash Match and Clustered Index Scan as you see from result part..<br />1.validconsumed-int<br />2.TreeID-int<br />3.Item_Code-varchar<br /><br /><br /><br />Result as follows:-<br /> <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"> |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />.[Supplier_Code], [P].[ItemCode], .[Region_Code], .[Lead_Time]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />((.[Supplier_Code]=.[Supplier_Code] AND [P].[ItemCode]=[P].[ItemCode]) AND .[Region_Code]=.[Region_Code]) AND .[Lead_Time]=[<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[P].[ItemCode])=(.[Item_Code]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[P].[ItemCode]=.[Item_Code]))<br /> |–Nested Loops(Left Semi Join, WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[P].[TreeId]=[ProdTreeName].[TreeId]))<br /> | |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[IXAppDB_Prod].[dbo].[ProductHierarchy] AS [P]))<br /> | |–Table Spool<br /> | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[IXAppDB_Prod].[dbo].[ProdTreeName].[PK_TreeID]))<br /> |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[IXAppDB_Prod].[dbo].[PL_Supplier_Product_Relationship] AS ), WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />.[ValidConsumed]=1))<br /><br />please advice…Thanks in advance folks<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote">

---

Is this DISTINCT really needed?<br />How many rows in the base tables are involved?<br />How many rows are supposed to be returned?<br /><br />Btw, personally I would prefer an explicit JOIN instead of the WHERE thingy. But that’s not really important. [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br /><br />–<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

---

Thanks Frank..
1. yes distinct is required cos duplicate rows are there.
2. 53819 rows in base table
3. approx.. more than 5500 rows.. I have changed the query like this…
regards

---

Guys I have another query.. along this previous one..

I have composite noncluster index on A.RegCode and A.suppcode columns and Cluster index on B.StockId.. It is showing Index scan on A.RegCode and A.suppcode columns from supplier table.
I need to make sure that is this ideal index apply or can be other better way to implement these index to be selective enough for query engine. you see I want to remove index seek rather than to index scan(it’s mean query do not using this index properly). regards…

---

]]>