SQL Server Performance Forum – Threads Archive
Counting rows and textual where clause
Hi, All! I have an application that "imports" xml data, the table contains about 1 milion rows; table looks lite this (snapshot) AttributeID ; intAttributeName ; varchar (50)
AttributeValue ;varchar 255
NodeID ; int (FK )
Attributecreated;
XMLDocumentGUID ; GUID
PackageID; 1 What i do is the following; SELECT Count(AttributeID) FROM Attributes WHERE AttributeValue = ‘SQLServer’ AND PackageID = 1 i also "join" XMLDocumentGUID on some other tables, but when usin SQL Query Analyser i can see that the bottleneck is in the AttributeTable, i also tested to run the IndexTuning Wizard, with no good result; Here is one of my statements; SELECT COUNT(Attributes.Attributevalue) AS InstallOn
FROM Attributes INNER JOIN
Nodes ON Attributes.NodeID = Nodes.NodeID INNER JOIN
vwUniqueRoots ON Nodes.NodecollectionGUID = vwUniqueRoots.fldRowSetGUID
WHERE (Attributes.Attributevalue = ‘Ad-aware 6 Personal’) AND (Attributes.PackageID = 4) Also tested to use group by and so on,
Question then; How do i best "index" this table, AttributeValue ? If then how?
Kind regards Math
Adding the current SQL Statement and The ExecutionPlan;<br /><br /><br />Statement:<br />=======<br /><br />SELECT COUNT(Attributes.Attributevalue) AS InstallOn<br />FROM Attributes INNER JOIN<br /> Nodes ON Attributes.NodeID = Nodes.NodeID INNER JOIN<br /> vwUniqueRoots ON Nodes.NodecollectionGUID = vwUniqueRoots.fldRowSetGUID<br />WHERE (Attributes.Attributevalue = ‘Ad-aware 6 Personal’) AND (Attributes.PackageID = 4)<br /><br /><br /><br />ExePlan:<br />=======<br /><br /><br /> <font color="green">|–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[Expr1006]=Convert([Expr1010])))<br /> |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1010]=Count(*)))<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Nodes].[NodecollectionGUID])=([Rootnodes].[RootnodecollectionGUID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Nodes].[NodecollectionGUID]=[Rootnodes].[RootnodecollectionGUID]))<br /> |–Nested Loops(Inner Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Attributes].[NodeID]))<br /> | |–Filter(WHERE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />Convert([Attributes].[PackageID])=4))<br /> | | |–Bookmark Lookup(BOOKMARK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Bmk1000]), OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[office_ASSET].[dbo].[Attributes]))<br /> | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[office_ASSET].[dbo].[Attributes].[Test_Index]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Attributes].[Attributevalue]=’Ad-aware 6 Personal’) ORDERED FORWARD)<br /> | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[office_ASSET].[dbo].[Nodes].[PK_Nodes]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Nodes].[NodeID]=[Attributes].[NodeID]) ORDERED FORWARD)<br /> |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[office_ASSET].[dbo].[Rootnodes].[PK_Rootnodes]))</font id="green">
How about to run ITW in SQL Analyzer to see what indexs are suggested?
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
]]>