Post reply

Performance Problem -- Help please!

  • April 7, 2005 at 8:30 am

    #551171

    The DISTINCTS give me the total number of SETS contained amongst the images I am selecting, and the total number of NEW SETS contained amongst the images I am selecting.

    I will try building the query step by step to see where it starts coming to it's knees. Will post back when I am through.

  • April 7, 2005 at 8:31 am

    #551172

    Thanks! This query functions normally, but the performance is no better than the others, unfortunately.

  • April 7, 2005 at 8:35 am

    #551173

    The query that seems to work the best up until now is one suggested by a member on another forum. It is below:

    select a.Category_name, a.Set_count, a.Category_id, a.Image_count, a.Image_id, b.New_Image_Count, b.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 
tblCategories c l
eft 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.Category_ID = b.Cat_ID
ORDER BY a.Category_Name

    I am now starting to rebuild my original query from scratch to see at exactly what point performance degrades. Will post back shortly.

    Thanks so much for everyone's assistance!

  • April 7, 2005 at 9:07 am

    #551188

    Ok. Here we go. Below I will show the first 3 steps of this query, and the time it is taking to execute.

    Step 1 (executing in less than 1 second):

    SELECT tblC.catDesc AS Category_Name, 
tblC.cat_ID AS Category_ID
FROM tblCategories tblC
WHERE tblC.skin_ID = @skin_ID
GROUP BY tblC.cat_id, tblC.catDesc
ORDER BY Category_Name

    Step 2 (executing in 0-2 seconds):

    SELECT tblC.catDesc AS Category_Name, 
tblC.cat_ID AS Category_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
ORDER BY Category_Name

    Step 3 (executing in 2-3 seconds):

    SELECT tblC.catDesc AS Category_Name, 
tblC.cat_ID AS Category_ID,
COUNT(tblI.Img_ID) AS Image_Count
FROM tblCategories tblC
INNER 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 Category_Name

    .. 3 seconds seems to be an awful lot for Step 3. All I'm adding is the COUNT(tblI.Img_ID). Should this really take a whole other second?

  • April 7, 2005 at 9:23 am

    #551197

    I remember seening elsewhere (sqlteam.com) that there is a 'special' difference between COUNT(*) and COUNT(tblI.Img_ID)...difference relates to something to do with counting/summing NULLS.

    Maybe the former is more suitable for you?

    Do you need to know the number of img_id's or the number of records returned from the image table?

    Maybe the extra time is taken to read and inspect a (non-indexed column??), when COUNT(*) just evaluates to whatever indexed column would satisfy the query.

  • April 7, 2005 at 9:36 am

    #551210

    Strange I checked the exec plan for both solutions

     

    declare @skin_ID int

    set @Skin_ID=1

    declare @d_t datetime

    set @d_t=getdate()

    select a.Category_name, a.Set_count, a.Category_id, a.Image_count, a.Image_id, b.New_Image_Count, b.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   tblCategories c 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&nbsp as b  on a.Category_ID = b.Cat_ID ORDER BY a.Category_Name

    SELECT catDesc as Category_Name,

    tC.cat_ID as Category_ID,

    ISNULL(SUM(Set_Count),0) as Set_Count,

    ISNULL(SUM(Image_Count),0) as Image_Count,

    MIN(Image_ID) as Image_ID,

    ISNULL(SUM(New_Image_Count),0) as New_Image_Count,

    ISNULL(SUM(New_Set_Count),0) as New_Set_Count

    FROM tblCategories tC left join

    (SELECT

    cat_ID AS Category_ID,

    1 AS Set_Count,

    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(CASE WHEN SUM(CASE WHEN tblI.d_t > @d_t THEN 1 END)>0 THEN 1 END,0) AS New_Set_Count

    FROM tblImage tblI

    GROUP BY cat_id, tblI.set_ID

    ) t

    on tC.cat_ID=t.Category_ID

    WHERE tC.skin_ID = @skin_ID

    GROUP BY tC.cat_ID, tC.catDesc

    ORDER BY tC.catDesc

    and the sec one looks really better.

    Have you added the INDEXES that I specified?

     

     

    Kindest Regards,

    Vasc

  • April 7, 2005 at 10:01 am

    #551219

    |--Compute Scalar(DEFINE[Expr1014]=isnull([Expr1009], 0), [Expr1015]=isnull([Expr1010], 0), [Expr1016]=isnull([Expr1012], 0), [Expr1017]=isnull([Expr1013], 0)))

    |--Nested Loops(Left Outer Join, OUTER REFERENCES[tC].[cat_ID]))

    |--Index Seek(OBJECT[Test1].[dbo].[tblCategories].[IX_tblCategories_2] AS [tC]), SEEK[tC].[skin_ID]=[@skin_ID]) ORDERED FORWARD)

    |--Compute Scalar(DEFINE[Expr1009]=If ([Expr1075]=0) then NULL else [Expr1076], [Expr1010]=If ([Expr1077]=0) then NULL else [Expr1078], [Expr1012]=If ([Expr1079]=0) then NULL else [Expr1080], [Expr1013]=If ([Expr1081]=0) then NULL else [Expr1082]))

    |--Stream Aggregate(DEFINE[Expr1075]=COUNT_BIG([Expr1006]), [Expr1076]=SUM([Expr1006]), [Expr1077]=COUNT_BIG([Expr1001]), [Expr1078]=SUM([Expr1001]), [Expr1011]=MIN([Expr1002]), [Expr1079]=COUNT_BIG([Expr1007]), [Expr1080]=SUM([Expr1007]), [Expr1081]=COUNT_BIG([Expr1008]), [Expr1082]=SUM([Expr1008])))

    |--Compute Scalar(DEFINE[Expr1006]=1, [Expr1007]=If ([Expr1003]NULL) then [Expr1003] else 0, [Expr1008]=If (If ([Expr1004]>0) then 1 else NULLNULL) then If ([Expr1004]>0) then 1 else NULL else 0))

    |--Compute Scalar(DEFINE[Expr1001]=Convert([Expr1070]), [Expr1003]=If ([Expr1071]=0) then NULL else [Expr1072], [Expr1004]=If ([Expr1073]=0) then NULL else [Expr1074]))

    |--Stream Aggregate(GROUP BY[tblI].[set_ID]) DEFINE[Expr1070]=Count(*), [Expr1002]=MIN([tblI].[Img_ID]), [Expr1071]=COUNT_BIG(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1072]=SUM(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1073]=COUNT_BIG(If ([tblI].[d_t]>[@d_t]) then 1 else NULL), [Expr1074]=SUM(If ([tblI].[d_t]>[@d_t]) then 1 else NULL)))

    |--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[Test1].[dbo].[tblImage] AS [tblI]))

    |--Index Seek(OBJECT[Test1].[dbo].[tblImage].[IX_tblImage] AS [tblI]), SEEK[tblI].[cat_ID]=[tC].[cat_ID]) ORDERED BACKWARD)

    Kindest Regards,

    Vasc

  • April 7, 2005 at 11:16 am

    #551240

    Can't determine any noticeable difference between COUNT(*) and COUNT(tblI.Img_ID) in Step 3 (above). Both seem to be executing in the same amount of time.

  • April 7, 2005 at 11:38 am

    #551244

    Did you want me to make that the clustered index, or just a regular index? Right now as the table exists on my server there are two indexes on tblImage:

    [Img_ID] - Clustered

    Fields: Img_ID

    [cat_id_set_id]

    Fields: cat_id, set_id

    When I execute your query with these indexes, it is doing a clustered index SCAN on the clustered index, and not using the cat_id_set_id index.

  • April 7, 2005 at 11:41 am

    #551245

    Making that index clustered, and removing the clustered index on Img_ID makes your query perform the fastest now. 80% of the time it executes in 1 seconds. The other 20% its 2+, but still the fastest for having all of the data.

  • April 8, 2005 at 7:35 am

    #551481

    Any other ideas, or is this about the quickest we are going to be able to get this?

  • April 8, 2005 at 8:26 am

    #551532

    The variation in time is that big because there is a BOOKMARK

    if there are more rows that match criteria this will take longer...

    Kindest Regards,

    Vasc

  • April 8, 2005 at 8:34 am

    #551537

    SELECT catDesc as Category_Name,

    tC.cat_ID as Category_ID,

    ISNULL(SUM(Set_Count),0) as Set_Count,

    ISNULL(SUM(Image_Count),0) as Image_Count,

    MIN(Image_ID) as Image_ID,

    ISNULL(SUM(New_Image_Count),0) as New_Image_Count,

    ISNULL(SUM(New_Set_Count),0) as New_Set_Count

    FROM tblCategories tC left join

    (

    select x.cat_ID AS Category_ID,Set_Count,Image_Count,Image_ID,New_Image_Count,New_Set_Count from

    (SELECT

    cat_ID,

    1 AS Set_Count,

    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(CASE WHEN SUM(CASE WHEN tblI.d_t > @d_t THEN 1 END)>0 THEN 1 END,0) AS New_Set_Count

    FROM tblImage tblI

    GROUP BY cat_id, tblI.set_ID

    ) x  left Join

    (

    SELECT

    cat_ID,

    SUM(tblI.display_status ) AS New_Image_Count,

    1 AS New_Set_Count

    FROM tblImage tblI

    WHERE tblI.d_t > @d_t

    GROUP BY cat_id, tblI.set_ID

    ) y on x.cat_ID=y.cat_ID

    ) t

    on tC.cat_ID=t.Category_ID

    WHERE tC.skin_ID = @skin_ID

    GROUP BY tC.cat_ID, tC.catDesc

    ORDER BY tC.catDesc

     

     

    Kindest Regards,

    Vasc

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply