SQL Server Performance Forum – Threads Archive
Performance Issue – Please help..
The query below is taking 3-4 seconds to run under a light load, whichseems to be a bit lengthy for the indexes that are in place and the
amount of data that exists in the tables. I have outlined everything
below, including all table definitions, indexes, and row counts. Any
help at all will be appreciated. It seems no matter how I think an
index will function it never seems to work properly. ==
BEGIN QUERY
==
SELECT tblC.catDesc AS Category_Name,
COUNT(DISTINCT tblS.set_ID) AS Set_Count,
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count,
MIN(tblI.Img_ID) AS Image_ID,
COALESCE(SUM(
CASE WHEN tblI.d_t > @d_t
AND tblI.display_status = 1 THEN
1
END
), 0) AS New_Image_Count,
COALESCE(COUNT(DISTINCT
CASE WHEN tblI.d_t > @d_t THEN
tblI.set_ID
END
), 0) AS New_Set_Count
FROM tblCategories tblC
LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
LEFT JOIN tblSets tblS ON tblI.set_ID = tblS.set_ID
WHERE tblC.skin_ID = @skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
ORDER BY Category_Name
==
END QUERY
== ==
tblImage (approx. 71000 rows)
==
Definition:
Img_ID (int, Not Null) – PK
set_ID (int, Null)
cat_ID (int, Null)
d_t (datetime, Null)
display_status (int, Null) Indexes:
1. Img_ID (clustered)
2. cat_id DESC, display_status DESC, d_t DESC
3. d_t DESC, display_status DESC, set_ID, cat_ID
4. set_ID DESC
==
END tblImage
== ==
tblCategories (approx. 35 rows)
==
Definition:
cat_ID (int, Not Null) – PK
catDesc (varchar(25), Null)
skin_ID (int, Null) Indexes:
1. cat_ID (clustered)
2. skin_ID, cat_ID
==
END tblCategories
== ==
tblSets (approx. 1500 rows)
==
Definition:
set_ID (int, Not Null) – PK
setName (varchar(25), Null)
setKeywords (varchar(500), Null) Indexes:
1. set_ID (clustered)
==
END tblSets
== Thanks!! — Shawn
try clustering
tblCategories on skin_ID, cat_ID
tblImage on cat_ID, Imag_ID
Ok. Just did what you suggested and it now takes 4 seconds consistently. When I put the indexes back to the way they were, it takes between 3 and 4 seconds.
For this specific query clustering tblImage.cat_id should offer the best performance (not taking in account fragmentation that may slow it down after enough changes are made). This query may be rearranged, but I don’t think it would make performance significantly better. Next code should be equivalent to yours:
SELECT tblC.catDesc AS Category_Name,
COUNT(DISTINCT tblI.set_ID) AS Set_Count,
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count,
MIN(tblI.Img_ID) AS Image_ID,
Count(CASE WHEN tblI.d_t > @d_t AND tblI.display_status = 1 THEN 1 END) AS New_Image_Count,
COUNT(DISTINCTCASE WHEN tblI.d_t > @d_t THEN tblI.set_ID END) AS New_Set_Count
FROM tblCategories tblC
LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
WHERE tblC.skin_ID = @skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
ORDER BY tblC.Category_Name
I have in mind some other possibilities how to change the query, but without real data I can’t estimate if that will help or make performance worst.
Try first the query and index design i mentioned and let me know if it helped.
try tblCategories cluster index to
skin_ID, catDesc, cat_ID also, did the execution plan show index seek on the clustered index?
Joe, tblCategories table contains only 35 rows.
mmarovic,<br /><br />tblImage.cat_id is already in the clustered index, and is the first field in this index.<br /><br />joechang,<br /><br />I have done what you have suggested, and it now takes 4-5 seconds to execute instead of 3-4. I have pasted the execution plan below:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[Expr1008]=If ([Expr1006]<>NULL) then [Expr1006] else 0, [Expr1009]=If ([Expr1007]<>NULL) then [Expr1007] else 0))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Merge Join(Inner Join, MANY-TO-MANY MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc])=([tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc] ASC))<br /> | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc])=([tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=Convert([Expr1044])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1044]=COUNT_BIG([tblS].[set_ID])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [tblS].[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [tblS].[set_ID]=[tblS].[set_ID]))<br /> | | |–Table Spool<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1020]=If ([tblI].[d_t]>[@d_t]) then [tblI].[set_ID] else NULL))<br /> | | |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblS].[set_ID])=([tblI].[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblI].[set_ID]=[tblS].[set_ID]))<br /> | | |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblSets].[PK_tblSets] AS [tblS]))<br /> | | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]) WITH PREFETCH)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [tblC]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[skin_ID]=[@skin_id]) ORDERED FORWARD)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[cat_id_img_id] AS [tblI]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblI].[cat_ID]=[tblC].[cat_ID]) ORDERED FORWARD)<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1004]=Convert([Expr1045]), [Expr1006]=If ([Expr1046]=0) then NULL else [Expr1047]))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1045]=COUNT_BIG([tblI].[Img_ID]), [Expr1046]=COUNT_BIG(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1047]=SUM(If ([tblI].[d_t]>[@d_t] AND [tblI].[d
isplay_status]=1) then 1 else NULL), [Expr1005]=MIN([tblI].[Img_ID])))<br /> | |–Table Spool<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1007]=Convert([Expr1048])))<br /> |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc], [tblC].[cat_ID]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1048]=COUNT_BIG([Expr1020])))<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc] ASC, [tblC].[cat_ID] ASC))<br /> |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [Expr1020]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [Expr1020]=[Expr1020]))<br /> |–Table Spool<br /></font id="code"></pre id="code">
I didn’t recommend to be in the clustered index, my recommendation is to be the only column of the clustered index.
On top of that you mentioned that pk (on Img_id) is clustered. Better take out left join with tblSets, you don’t need it.
mmarovic,<br /><br />Ok. I did misunderstand. After making tblImages.cat_id the ONLY field in the clustered index, and take out the join on tblSets I am able to execute the query consistently in 3 secs. Still, I feel, much longer than it should be. This should execute in a second maximum, right? Here is the new query and execution plan<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT tblC.catDesc AS Category_Name, <br />COUNT(DISTINCT tblI.set_ID) AS Set_Count,<br />tblC.cat_ID AS Category_ID,<br />COUNT(tblI.Img_ID) AS Image_Count,<br />MIN(tblI.Img_ID) AS Image_ID,<br />COALESCE(SUM(<br />CASE WHEN tblI.d_t > @d_t<br />AND tblI.display_status = 1 THEN<br />1<br />END<br />), 0) AS New_Image_Count,<br />COALESCE(COUNT(DISTINCT<br />CASE WHEN tblI.d_t > @d_t THEN<br />tblI.set_ID<br />END<br />), 0) AS New_Set_Count<br />FROM tblCategories tblC<br />LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID<br />WHERE tblC.skin_ID = @skin_ID<br />GROUP BY tblC.cat_id, tblC.catDesc<br />ORDER BY Category_Name<br /><br /><br /><br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[Expr1007]=If ([Expr1005]<>NULL) then [Expr1005] else 0, [Expr1008]=If ([Expr1006]<>NULL) then [Expr1006] else 0))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Merge Join(Inner Join, MANY-TO-MANY MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc])=([tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc] ASC))<br /> | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc])=([tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1002]=Convert([Expr1037])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1037]=COUNT_BIG([tblI].[set_ID])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [tblI].[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [tblI].[set_ID]=[tblI].[set_ID]))<br /> | | |–Table Spool<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1019]=If ([tblI].[d_t]>[@d_t]) then [tblI].[set_ID] else NULL))<br /> | | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]) WITH PREFETCH)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [tblC]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[skin_ID]=[@skin_id]) ORDERED FORWARD)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[cat_id_img_id] AS [tblI]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblI].[cat_ID]=[tblC].[cat_ID]) ORDERED FORWARD)<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=Convert([Expr1038]), [Expr1005]=If ([Expr1039]=0) then NULL else [Expr1040]))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1038]=COUNT_BIG([tblI].[Img_ID]), [Expr1039]=COUNT_BIG(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1040]=SUM(If ([tblI].[d_t
]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1004]=MIN([tblI].[Img_ID])))<br /> | |–Table Spool<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1006]=Convert([Expr1041])))<br /> |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc], [tblC].[cat_ID]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1041]=COUNT_BIG([Expr1019])))<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc] ASC, [tblC].[cat_ID] ASC))<br /> |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [Expr1019]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [Expr1019]=[Expr1019]))<br /> |–Table Spool<br /><br /><br /></font id="code"></pre id="code">
quote:Still, I feel, much longer than it should be. This should execute in a second maximum, right?
You are right. You haven’t tried the complete query I posted, but I think it shouldn’t be visible difference.
I have to go now, I’ll take a look at execution plan tomorrow if someone else doesn’t solve the problem already.
Do you have a lot of images per category? The slownes might come from fragmentation, not enough RAM, tempdb fragmentation, locking… Who knows, we would need much more info to judge.
mmavoric, I sincerely appreciate your time and help. As for the images in each category, some categories have as few as 50 images, while others have as many as 17186. Thanks again! — Shawn
on the matter of clustered indexes, SQL Server will make the cluster key unique if the key you provide is not, hence you may as well include a reasonably small column to make it unique.
i think a problem i did not account for first is the DISTINCT on the Set_ID
try clustering tblImage on cat_ID, set_ID (may be followed by Img_ID) also, try to make each of cat_ID, set_ID and d_t NOT NULL if possible
i don’t think it will be possible to squeeze much more out of this because it is not a simple query, the d_t condition makes it difficult to index well
you might also try clustering tblImage
on cat_ID, d_t
hard to tell which of the 2 is better
Replace the COALESCE function with a CASE statement, much quicker IME.
Run this with SET STATISTICS IO ON and post the results please. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
There is no need for coalesce function. I don’t expect significant improvement from removing it but i’ve already posted the code without coalesce.
It works because count never returns null as other agragate functions do, but filters out null values, so count only rows for which expression inside count is not null. You can also try:
select a.CatDesc, a.Category_name, a.Set_count, a.Category_id, a.Image_count, a.Image_id, b.New_Image_Count, c.New_Set_Count
from (
SELECT tblC.catDesc AS Category_Name,
COUNT(DISTINCT tblI.set_ID) AS Set_Count,
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count,
MIN(tblI.Img_ID) AS Image_ID
FROM tblCategories tblC
LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID
WHERE tblC.skin_ID = @skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
) as a
join (
select
c.Cat_id,
count(case when i.display_status = 1 then -55 end)as New_Image_Count,
count(distinct i.Set_id)as New_Set_Count
from
tblCategoriesc left join
tblImage i on i.Cat_ID = c.Cat_ID and
i.d_t > @d_t
where
c.skin_ID = @skin_ID
group by
c.Cat_id
) as b
on a.Cat_ID = b.Cat_ID
ORDER BY a.Category_Name
And crete indexes on tblImage:
1. Img_id primary key clustered
2. Cat_ID, Set_ID
3. You should test what is better and apply one of:
a) Cat_ID, d_t, Display_Status, Set_ID
b) d_t, Cat_ID, Display_Status, Set_ID Why that may be faster? Because you can better tailor indexes for each derived table. It also can be slower because you will have 2 group by queries instead one.
Hard to predict, it has to be tested. If Adriaan is right about coalesce consider testing my first script too.
Ok, all. I have replaced COALESCE with COUNT as suggested, and unfortunately there is no noticeable difference.<br /><br />Here is an update of what the query is now:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT tblC.catDesc AS Category_Name, <br />COUNT(DISTINCT tblI.set_ID) AS Set_Count,<br />tblC.cat_ID AS Category_ID,<br />COUNT(tblI.Img_ID) AS Image_Count,<br />MIN(tblI.Img_ID) AS Image_ID,<br />COUNT(<br />CASE WHEN tblI.d_t > @d_t<br />AND tblI.display_status = 1 THEN<br />1<br />END<br />) AS New_Image_Count,<br />COUNT(DISTINCT<br />CASE WHEN tblI.d_t > @d_t THEN<br />tblI.set_ID<br />END<br />) AS New_Set_Count<br />FROM tblCategories tblC<br />LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID<br />WHERE tblC.skin_ID = @skin_ID<br />GROUP BY tblC.cat_id, tblC.catDesc<br />ORDER BY Category_Name <br /></font id="code"></pre id="code"><br /><br />Here is the output from SET STATISTICS IO ON:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />Table ‘Worktable’. Scan count 27, logical reads 56, physical reads 0, read-ahead reads 0.<br />Table ‘Worktable’. Scan count 3, logical reads 73434, physical reads 0, read-ahead reads 0.<br />Table ‘tblImage’. Scan count 14, logical reads 417, physical reads 0, read-ahead reads 0.<br />Table ‘tblCategories’. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.<br /></font id="code"></pre id="code"><br /><br />.. And, here is the new execution plan:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Merge Join(Inner Join, MANY-TO-MANY MERGE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc])=([tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc] ASC))<br /> | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc])=([tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1002]=Convert([Expr1023])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1023]=COUNT_BIG([tblI].[set_ID])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [tblI].[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [tblI].[set_ID]=[tblI].[set_ID]))<br /> | | |–Table Spool<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1007]=If ([tblI].[d_t]>[@d_t]) then [tblI].[set_ID] else NULL))<br /> | | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]) WITH PREFETCH)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [tblC]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[skin_ID]=[@skin_id]) ORDERED FORWARD)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[cat_id] AS [tblI]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblI].[cat_ID]=[tblC].[cat_ID]) ORDERED FORWARD)<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=Convert([Expr1024]), [Expr1005]=Convert([Expr1025])))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ a
lt=’‘ />[Expr1024]=COUNT_BIG([tblI].[Img_ID]), [Expr1025]=COUNT_BIG(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1004]=MIN([tblI].[Img_ID])))<br /> | |–Table Spool<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1006]=Convert([Expr1026])))<br /> |–Stream Aggregate(GROUP BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc], [tblC].[cat_ID]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1026]=COUNT_BIG([Expr1007])))<br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc] ASC, [tblC].[cat_ID] ASC))<br /> |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [Expr1007]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [Expr1007]=[Expr1007]))<br /> |–Table Spool<br /></font id="code"></pre id="code"><br /><br />We’re still working on 3 seconds to execute this query. I feel like we’re up against the wall on this one. Is this just the best SQL Server can do? Problems like thie really bother me as we have other queries that perform calculations on much more data that run a lot quicker. It seems very difficult to tune these indexes properly, no?
Run your two CASE COUNT statements seperately and see what the results are. Take out everything else and just run those. Don’t even join to the tblCategories table. How long does that take? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
quote:I have replaced COALESCE with COUNT as suggested, and unfortunately there is no noticeable difference.
Not surprised. As I thought additional processing due to whatever extra function applied is insignificant compared to I/O amount
that depends on how many data are processed (index used and join algorithm). Please try last query and configuration I suggested. If that doesn’t help we would need info about your server, db settings, perfmon counters etc.
Let’s first try queries and index design mentioned.
<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by derrickleggett</i><br /><br />Run your two CASE COUNT statements seperately and see what the results are. Take out everything else and just run those. Don’t even join to the tblCategories table. How long does that take?<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Derrick,<br /><br />I have done what you have suggested. The query takes 1-2 seconds to execute on the full table. Here is the exact query:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />SELECT COUNT(<br />CASE WHEN tblI.d_t > @d_t<br />AND tblI.display_status = 1 THEN<br />1<br />END<br />) AS New_Image_Count,<br />COUNT(DISTINCT<br />CASE WHEN tblI.d_t > @d_t THEN<br />tblI.set_ID<br />END<br />) AS New_Set_Count<br />FROM tblImage tblI<br /></font id="code"></pre id="code"><br /><br />.. And the execution plan:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br /> |–Nested Loops(Inner Join)<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[Expr1001]=Convert([Expr1008])))<br /> | |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1008]=COUNT_BIG(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL)))<br /> | |–Table Spool<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=If ([tblI].[d_t]>[@d_t]) then [tblI].[set_ID] else NULL))<br /> | |–Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[d_t_display_status_set_id_cat_id] AS [tblI]), ORDERED FORWARD)<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1002]=Convert([Expr1009])))<br /> |–Stream Aggregate(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1009]=COUNT_BIG([Expr1003])))<br /> |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=[Expr1003]))<br /> |–Table Spool<br /></font id="code"></pre id="code"><br /><br />Thanks.<br />
mmarovic,<br /><br />I have done as you have suggested. Had to tweak the query a bit and fix a few typos, but here it is:<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />select a.Category_name, a.Set_count, a.Category_id, a.Image_count, a.Image_id, b.New_Image_Count, b.New_Set_Count<br />from (<br />SELECT tblC.catDesc AS Category_Name, <br />COUNT(DISTINCT tblI.set_ID) AS Set_Count,<br />tblC.cat_ID AS Category_ID,<br />COUNT(tblI.Img_ID) AS Image_Count,<br />MIN(tblI.Img_ID) AS Image_ID<br />FROM tblCategories tblC<br />LEFT JOIN tblImage tblI ON tblC.cat_ID = tblI.cat_ID<br />WHERE tblC.skin_ID = @skin_ID<br />GROUP BY tblC.cat_id, tblC.catDesc<br />) as a<br />join (<br />select <br />c.Cat_id,<br />count(case when i.display_status = 1 then -55 end) as New_Image_Count,<br />count(distinct i.Set_id) as New_Set_Count<br />from <br />tblCategoriesc left join <br />tblImage i on i.Cat_ID = c.Cat_ID and<br /> i.d_t > @d_t<br />where <br />c.skin_ID = @skin_ID<br />group by <br />c.Cat_id<br />) as b<br /> on a.Category_ID = b.Cat_ID<br />ORDER BY a.Category_Name<br /></font id="code"></pre id="code"><br /><br />.. Here is the execution plan using the index: Cat_ID, d_t, Display_Status, Set_ID<br /><pre id="code"><font face="courier" size="2" id="code"><br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’
‘ />[tblC].[catDesc] ASC))<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID])=([tblC].[cat_ID]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID])=([c].[cat_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1008]=Convert([Expr1030])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1030]=COUNT_BIG(<i>.[set_ID])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID], <i>.[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID] AND <i>.[set_ID]=<i>.[set_ID]))<br /> | | |–Table Spool<br /> | | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]) WITH PREFETCH)<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [c]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[skin_ID]=[@skin_id]) ORDERED FORWARD)<br /> | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[cat_id_d_t_display_status_set_id] AS <i>), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><i>.[cat_ID]=[c].[cat_ID] AND <i>.[d_t] > [@d_t]) ORDERED FORWARD)<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1007]=Convert([Expr1031])))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1031]=COUNT_BIG(If (<i>.[display_status]=1) then -55 else NULL)))<br /> | |–Table Spool<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc])=([tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1002]=Convert([Expr1032])))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emo
ticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1032]=COUNT_BIG([tblI].[set_ID])))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [tblI].[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [tblI].[set_ID]=[tblI].[set_ID]))<br /> | |–Table Spool<br /> | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]) WITH PREFETCH)<br /> | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [tblC]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[skin_ID]=[@skin_id]) ORDERED FORWARD)<br /> | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[cat_id_set_id] AS [tblI]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblI].[cat_ID]=[tblC].[cat_ID]) ORDERED FORWARD)<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=Convert([Expr1033])))<br /> |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1033]=COUNT_BIG([tblI].[Img_ID]), [Expr1004]=MIN([tblI].[Img_ID])))<br /> |–Table Spool<br /><br /></font id="code"></pre id="code"><br /><br />.. Here is the execution plan using the index: d_t, Cat_ID, Display_Status, Set_ID<br /><pre id="code"><font face="courier" size="2" id="code"><br /> |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[catDesc] ASC))<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID])=([tblC].[cat_ID]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID])=([c].[cat_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1008]=Convert([Expr1030])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1030]=COUNT_BIG(<i>.[set_ID])))<br /> | | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID], <i>.[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID] AND <i>.[set_ID]=<i>.[set_ID]))<br /> | | |–Table Spool<br /> | | |–Hash Match(Left Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID])=(<i>.[cat_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=<i>.[cat_ID]))<br /> | | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [c]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[skin_ID]=[@skin_id]) ORDERED FORWARD)<br /> | | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[d_t_cat_id_display_status_set_id] AS <i>), SEEK<img s
rc=’/community/emoticons/emotion-6.gif’ alt=’‘ /><i>.[d_t] > [@d_t]) ORDERED FORWARD)<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]=[c].[cat_ID]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1007]=Convert([Expr1031])))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[c].[cat_ID]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1031]=COUNT_BIG(If (<i>.[display_status]=1) then -55 else NULL)))<br /> | |–Table Spool<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Hash Match(Inner Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc])=([tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> | |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1002]=Convert([Expr1032])))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1032]=COUNT_BIG([tblI].[set_ID])))<br /> | |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc], [tblI].[set_ID]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [tblI].[set_ID]=[tblI].[set_ID]))<br /> | |–Table Spool<br /> | |–Nested Loops(Left Outer Join, OUTER REFERENCES<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]) WITH PREFETCH)<br /> | |–Clustered Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [tblC]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[skin_ID]=[@skin_id]) ORDERED FORWARD)<br /> | |–Index Seek(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[dbImageArchive].[dbo].[tblImage].[cat_id_set_id] AS [tblI]), SEEK<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblI].[cat_ID]=[tblC].[cat_ID]) ORDERED FORWARD)<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))<br /> |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1003]=Convert([Expr1033])))<br /> |–Hash Match(Aggregate, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’‘ />[Expr1033]=COUNT_BIG([tblI].[Img_ID]), [Expr1004]=MIN([tblI].[Img_ID])))<br /> |–Table Spool<br /><br /></font id="code"></pre id="code"><br /><br />Both queries seem to execute in about the same amount of times, approx. 2 seconds. This means we have made some improvement, but still not under a second as I think it should be. Tell me what info you’d like about the server that would help you help me to determine if this is the cause. <br /><br />I can’t iterate enough how much I appreciate everyones help.
Since you are gathering aggregates, why not run separate queries to get the different counts? You could just do a SELECT, store @@ROWCOUNT in a holding variable, then do another SELECT and store @@ROWCOUNT in another holding variable, and return those variables. My guess is that the biggest time is spent on COUNT(DISTINCT …). You may want to look into setting that query up in reverse: SELECT * FROM tblI WHERE EXISTS (SELECT * FROM tblC WHERE …….)
When I take out both DISTINCTS I am able to get the query down to 1 second. So we’re definitely making progress, but the problem is that I need this value, so I can’t just take it out, haha.
Correct me if I’m wrong but the COUNT(DISTINCT) looks to be the same thing as doing a COUNT on your reference table (‘lookup table’) for the images – of course not ignoring the criteria. Reference tables in general have fewer rows than regular data entry tables. Therefore it should take less time to count the rows in a reference table that have a match in a data entry table, than to count the number of distinct values in the rows in the data entry table. Using a WHERE EXISTS clause further speeds things up because one match is sufficient, whereas a JOIN would always locate all matching rows before being satisfied.
quote:Originally posted by Adriaan
Since you are gathering aggregates, why not run separate queries to get the different counts? You could just do a SELECT, store @@ROWCOUNT in a holding variable, then do another SELECT and store @@ROWCOUNT in another holding variable, and return those variables.
My guess is that the biggest time is spent on COUNT(DISTINCT …). You may want to look into setting that query up in reverse:
SELECT * FROM tblI WHERE EXISTS (SELECT * FROM tblC WHERE …….)
I’m not sure where in the query this would fit in. I do understand your point, but am not sure if it can be applied with the current table schema we have. There would be no way for me to query tblSets to determine the amount of new sites in a particular category, or the total number of sets in any particular category. This data is stored in tblImage. Here is a repost of the table schema: tblImage:
Img_ID (int)
set_ID (int)
cat_ID (int)
d_t (datetime)
display_status (int) tblSets:
set_ID (int)
setName (varchar(25))
setKeywords (varchar(500)) tblCategories:
cat_ID (int)
catDesc (varchar(25))
skin_ID (int) Shawn
quote:Both queries seem to execute in about the same amount of times, approx. 2 seconds. This means we have made some improvement, but still not under a second as I think it should be. Tell me what info you’d like about the server that would help you help me to determine if this is the cause.
Ok, start with hardware configuration (cpu, RAM, is server dedicated to mssql server, disk configuration).
quote:Originally posted by mmarovic
quote:Both queries seem to execute in about the same amount of times, approx. 2 seconds. This means we have made some improvement, but still not under a second as I think it should be. Tell me what info you’d like about the server that would help you help me to determine if this is the cause.
Ok, start with hardware configuration (cpu, RAM, is server dedicated to mssql server, disk configuration).Ok, here goes: There are two servers that are clustered. Clustering is setup in an Active/Passive design. Only one server is actively serving SQL requests at one time. The other server remains as a backup. Both servers are identical hardware wise, and completely dedicated to MSSQL: CPU: Quad Intel PIIIE Xeon-A 700mhz
Memory: 1gb
OS Drives: 2 18gb drives – RAID 1 (mirroring)
MSSQL Storage (stored in external disk array): 86gb MSSQL storage (RAID 5) 20.5gb free Just a quick note that CPU utilization is typically very low, RARELY peaking at 70%, but mostly between 0-30%.
I thought you might have weaker hardware then it is the case. RAID 5 is not the most optimal solution, but I guess you don’t have a choice. But back to the query first: Have you cleared the cache before running the query?
Yes, I am. I am running the following each time before I execute the query: DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
You might have added
checkpoint
go
in front. Anyway, have you tried to repeat query execution after without clearing the cache? What is execution time when data are cached?
What would be the purpose of executing "CHECKPOINT" before both DBCC commands? I looked it up and it states that it forces the writing to disk of all dirty pages in the current database. How would that affect query performance? In any case, I will run more tests without clearing the cache once I get in the office this morning. I’ll update this thread then. Thanks!
quote:What would be the purpose of executing "CHECKPOINT" before both DBCC commands? I looked it up and it states that it forces the writing to disk of all dirty pages in the current database. How would that affect query performance?
In case someone updated data you are working with they will not be cleaned from cache using DBCC DROPCLEANBUFFERS. Checkpoint will make all buffers clean.
Makes sense. Thanks for the explanation. It is taking 3-5 seconds to run with the following commands preceding the query: CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Without these, it takes between 2-3 seconds to run. 3 seconds more times than 2. Shawn
No other ideas?
Try using a sub query as Adriaan suggested, does something like: SELECT tblC.catDesc AS Category_Name,
COUNT(DISTINCT tblI.Load_Name) AS Set_Count,
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count,
MIN(tblI.Img_ID) AS Image_ID,
SUM(New_Image_Count) AS New_Image_Count,
COUNT(new_data.set_ID) AS New_Set_Count
FROM tblCategories tblC
LEFT JOIN tblImage tblI
ON tblC.cat_ID = tblI.cat_ID
LEFT JOIN (SELECT cat_ID,
set_ID,
SUM(CASE WHEN tblI.display_status = 1 THEN 1
ELSE 0
END) AS New_Image_Count
FROM tblImage
WHERE d_t > @d_t) new_data
ON tblC.cat_id = new_data.cat_id
WHERE tblC.skin_ID = @skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
ORDER BY Category_Name … give you what you need? How long does the sub query: SELECT cat_ID,
set_ID,
SUM(CASE WHEN tblI.display_status = 1 THEN 1
ELSE 0
END) AS New_Image_Count
FROM tblImage
WHERE d_t > @d_t …take to run? I don’t have the tables etc to test it so it may not return quite what you want (or indeed work) but the idea is, rather than to do DISTINCT, to calculate your sums and counts where d_t > @d_t in a sub query and then use this. However it may not do any better than the query optimiser. If the field [display_status] never has a value greater than 1 you could use INSULL(display_status, 0) instead, sometimes quicker than CASE statements. Regards, Robert.
What about execution time when data are cached?
You should add up to 3 GB memory, 1 GB is too little.
]]>