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

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

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

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.

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

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

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

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 am flushing the buffers and cache with:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

Performance has not really improved. Still 3 seconds to execute

I have removed img_id from the clustered index on tblImage.

I have also changed all indexes on this table so the sort order is ASC instead of DESC. The reason I...

Just to update everybody, I have modified the query a bit to 86 the join on tblSets:

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,
COALESCE(SUM(
CASE...

Not sure if I understand your question directly, but here is what I think you are asking for:

tblCategories (C) and tblSets (S) do not relate to each-other directly. Records in...

Ok.. Think I got it.. Is this what you're lookin for? It's probably going to wrap and look a little odd.

  |--Sort(ORDER BY[tblC].[catDesc]...

Receiving the following error:

'SHOWPLAN' is not a recognized option.

when trying to execute:

SET SHOWPLAN ON

prior to the query. Sorry guys, I'm a little new at reading execution plans.

Very impressed with this test. Thanks, Wes.

I am in the market for one of these products and was liking the results of MiniSQLBackup shown in your test. I am, however,...