Performance Problem

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,
(
SELECT COUNT(tblI2.Img_ID)
FROM tblImage tblI2
LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
WHERE tblI2.d_t > @.d_t
AND tblI2.cat_ID = tblC.cat_ID
AND tblI2.display_status = 1
) AS New_Image_Count,
(
SELECT COUNT(DISTINCT tblI3.set_ID)
FROM tblImage tblI3
LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
WHERE tblI3.d_t > @.d_t
AND tblI3.cat_ID = tblC.cat_ID
) 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
==
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
==If I understand the query correctly, (If set_ID is unique in tblSets) Then
the following might work and should be faster since it doesn't have the
subquerys...
Select C.catDesc Category_Name,
Count(Distinct S.set_ID) Set_Count,
C.cat_ID Category_ID,
Count(tblI.Img_ID) Image_Count,
Min(I.Img_ID) Image_ID,
Sum(Case When I.d_t = @.d_t
And display_status = 1
Then 1 End) New_Image_Count,
Sum(Case When I.d_t = @.d_t
Then 1 End) New_Set_Count
From tblCategories C
Left Join tblImage I
On I.cat_ID = C.cat_ID
Left Join tblSets S
On S.set_ID = I.set_ID
Where C.skin_ID = @.skin_ID
Group By C.cat_id, C.catDesc
"iTISTIC@.gmail.com" wrote:

> 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,
> (
> SELECT COUNT(tblI2.Img_ID)
> FROM tblImage tblI2
> LEFT JOIN tblSets tblS2 ON tblS2.set_ID = tblI2.set_ID
> WHERE tblI2.d_t > @.d_t
> AND tblI2.cat_ID = tblC.cat_ID
> AND tblI2.display_status = 1
> ) AS New_Image_Count,
> (
> SELECT COUNT(DISTINCT tblI3.set_ID)
> FROM tblImage tblI3
> LEFT JOIN tblSets tblS3 ON tblS3.set_ID = tblI3.set_ID
> WHERE tblI3.d_t > @.d_t
> AND tblI3.cat_ID = tblC.cat_ID
> ) 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
> ==
> 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
> ==
>