SQL Server Performance

Performance Issue - Please help..

Discussion in 'T-SQL Performance Tuning for Developers' started by iTISTIC, Apr 4, 2005.

  1. iTISTIC New Member

    The query below is taking 3-4 seconds to run under a light load, which
    seems 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
  2. joechang New Member

    try clustering
    tblCategories on skin_ID, cat_ID
    tblImage on cat_ID, Imag_ID
  3. iTISTIC New Member

    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.
  4. mmarovic Active Member

    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.
  5. joechang New Member

    try tblCategories cluster index to
    skin_ID, catDesc, cat_ID

    also, did the execution plan show index seek on the clustered index?
  6. mmarovic Active Member

    Joe, tblCategories table contains only 35 rows.
  7. iTISTIC New Member

    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]&lt;&gt;NULL) then [Expr1006] else 0, [Expr1009]=If ([Expr1007]&lt;&gt;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]&gt;[@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]&gt;[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1047]=SUM(If ([tblI].[d_t]&gt;[@d_t] AND [tblI].[display_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">
  8. mmarovic Active Member

    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.
  9. iTISTIC New Member

    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 &gt; @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 &gt; @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]&lt;&gt;NULL) then [Expr1005] else 0, [Expr1008]=If ([Expr1006]&lt;&gt;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]&gt;[@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]&gt;[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1040]=SUM(If ([tblI].[d_t]&gt;[@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">
  10. mmarovic Active Member

    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.
  11. iTISTIC New Member

    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
  12. joechang New Member

    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
  13. joechang New Member

    you might also try clustering tblImage
    on cat_ID, d_t
    hard to tell which of the 2 is better
  14. Adriaan New Member

    Replace the COALESCE function with a CASE statement, much quicker IME.
  15. derrickleggett New Member

    Run this with SET STATISTICS IO ON and post the results please.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  16. mmarovic Active Member

    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.
  17. iTISTIC New Member

    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 &gt; @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 &gt; @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]&gt;[@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' alt=':(' />[Expr1024]=COUNT_BIG([tblI].[Img_ID]), [Expr1025]=COUNT_BIG(If ([tblI].[d_t]&gt;[@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?
  18. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  19. mmarovic Active Member

    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.
  20. iTISTIC New Member

    <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 />derrickleggett@hotmail.com<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 &gt; @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 &gt; @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]&gt;[@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]&gt;[@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 />
  21. iTISTIC New Member

    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 &gt; @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] &gt; [@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 />.. 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 src='/community/emoticons/emotion-6.gif' alt=':(' /><i>.[d_t] &gt; [@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.
  22. Adriaan New Member

    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 .......)
  23. iTISTIC New Member

    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.
  24. Adriaan New Member

    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.
  25. iTISTIC New Member

    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
  26. mmarovic Active Member

    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).

  27. iTISTIC New Member

    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%.
  28. mmarovic Active Member

    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?
  29. iTISTIC New Member

    Yes, I am. I am running the following each time before I execute the query:

    DBCC DROPCLEANBUFFERS
    DBCC FREEPROCCACHE

  30. mmarovic Active Member

    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?
  31. iTISTIC New Member

    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!
  32. mmarovic Active Member

    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.
  33. iTISTIC New Member

    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
  34. iTISTIC New Member

    No other ideas?
  35. RGKN New Member

    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.


  36. mmarovic Active Member

    What about execution time when data are cached?
  37. pyao88 New Member

    You should add up to 3 GB memory, 1 GB is too little.

Share This Page