Performance Problem -- Help please!
-
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
-
Can you run with SET SHOWPLAN ON and post the results ? It would be useful to see what execution plan the optimizer is actually choosing.
-
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.
-
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] ASC))
|--Compute Scalar(DEFINE[Expr1008]=If ([Expr1006]NULL) then [Expr1006] else 0, [Expr1009]=If ([Expr1007]NULL) then [Expr1007] else 0))
|--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID]))
|--Hash Match(Inner Join, HASH[tblC].[cat_ID])=([tblC].[cat_ID]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID]))
|--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID]))
| |--Compute Scalar(DEFINE[Expr1007]=Convert([Expr1038])))
| |--Stream Aggregate(GROUP BY[tblC].[cat_ID]) DEFINE[Expr1038]=COUNT_BIG([Expr1020])))
| |--Sort(ORDER BY[tblC].[cat_ID] ASC))
| |--Hash Match(Aggregate, HASH[tblC].[cat_ID], [Expr1020]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID] AND [Expr1020]=[Expr1020]))
| |--Table Spool
| |--Compute Scalar(DEFINE[Expr1020]=If ([tblI].[d_t]>[@d_t]) then [tblI].[set_ID] else NULL))
| |--Hash Match(Right Outer Join, HASH[tblS].[set_ID])=([tblI].[set_ID]), RESIDUAL[tblI].[set_ID]=[tblS].[set_ID]))
| |--Clustered Index Scan(OBJECT[dbImageArchive].[dbo].[tblSets].[PK_tblSets] AS [tblS]))
| |--Hash Match(Left Outer Join, HASH[tblC].[cat_ID])=([tblI].[cat_ID]), RESIDUAL[tblC].[cat_ID]=[tblI].[cat_ID]))
| |--Bookmark Lookup(BOOKMARK[Bmk1000]), OBJECT[dbImageArchive].[dbo].[tblCategories] AS [tblC]))
| | |--Index Seek(OBJECT[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [tblC]), SEEK[tblC].[skin_ID]=[@skin_id]) ORDERED FORWARD)
| |--Index Scan(OBJECT[dbImageArchive].[dbo].[tblImage].[d_t_display_status_set_id_cat_id] AS [tblI]))
|--Hash Match(Inner Join, HASH[tblC].[cat_ID])=([tblC].[cat_ID]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID]))
|--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID]))
| |--Compute Scalar(DEFINE[Expr1003]=Convert([Expr1039])))
| |--Hash Match(Aggregate, HASH[tblC].[cat_ID]) DEFINE[Expr1039]=COUNT_BIG([tblS].[set_ID])))
| |--Hash Match(Aggregate, HASH[tblC].[cat_ID], [tblS].[set_ID]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblS].[set_ID]=[tblS].[set_ID]))
| |--Table Spool
|--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID]))
|--Compute Scalar(DEFINE[Expr1004]=Convert([Expr1040]), [Expr1006]=If ([Expr1041]=0) then NULL else [Expr1042]))
|--Hash Match(Aggregate, HASH[tblC].[cat_ID]) DEFINE[Expr1040]=COUNT_BIG([tblI].[Img_ID]), [Expr1041]=COUNT_BIG(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1042]=SUM(If ([tblI].[d_t]>[@d_t] AND [tblI].[display_status]=1) then 1 else NULL), [Expr1005]=MIN([tblI].[Img_ID]), [tblC].[catDesc]=ANY([tblC].[catDesc])))
|--Table Spool
-
How are you correlating C to S?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
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 tblImages (I) are related to tblCategories (C) and tblSets (S), hence the SQL statement:
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
-
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 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 WHERE tblC.skin_ID = @skin_ID GROUP BY tblC.cat_id, tblC.catDesc ORDER BY Category_Name
-
I'd query your need to 'cluster' the tblimage on imageid....unless it adds some benefit to another part of your application. There may be a better candidate for a clustered index....IF ANY should be used.
Also the Index Scan on "dbImageArchive].[dbo].[tblImage].[d_t_display_status_set_id_cat_id] AS [tblI]))" isn't good news. You should be looking to get this as index seek.....(maybe the DESC part of the index definition doesn't help....why DESC anyway?....surely ordering for output is a function of any SELECT statement that operates on that data)
-
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 made some fields DESC was because that the newest records are going to be accessed the most, and I thought it would be better if those would be at the top of the index, no?
In any case, here is the updated query and new execution plan:
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 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 WHERE tblC.skin_ID = @skin_ID GROUP BY tblC.cat_id, tblC.catDesc ORDER BY Category_Name |--Compute Scalar(DEFINE
[Expr1007]=If ([Expr1005]NULL) then [Expr1005] else 0, [Expr1008]=If ([Expr1006]NULL) then [Expr1006] else 0))
|--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))
|--Merge Join(Inner Join, MANY-TO-MANY MERGE[tblC].[catDesc])=([tblC].[catDesc]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))
|--Sort(ORDER BY[tblC].[catDesc] ASC))
| |--Hash Match(Inner Join, HASH[tblC].[cat_ID], [tblC].[catDesc])=([tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]))
| |--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))
| | |--Compute Scalar(DEFINE[Expr1002]=Convert([Expr1037])))
| | |--Hash Match(Aggregate, HASH[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE[Expr1037]=COUNT_BIG([tblI].[set_ID])))
| | |--Hash Match(Aggregate, HASH[tblC].[cat_ID], [tblC].[catDesc], [tblI].[set_ID]), RESIDUAL([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [tblI].[set_ID]=[tblI].[set_ID]))
| | |--Table Spool
| | |--Compute Scalar(DEFINE[Expr1019]=If ([tblI].[d_t]>[@d_t]) then [tblI].[set_ID] else NULL))
| | |--Nested Loops(Left Outer Join, OUTER REFERENCES[tblC].[cat_ID]) WITH PREFETCH)
| | |--Clustered Index Seek(OBJECT[dbImageArchive].[dbo].[tblCategories].[skin_ID_cat_ID] AS [tblC]), SEEK[tblC].[skin_ID]=[@skin_id]) ORDERED FORWARD)
| | |--Clustered Index Seek(OBJECT[dbImageArchive].[dbo].[tblImage].[cat_id_img_id] AS [tblI]), SEEK[tblI].[cat_ID]=[tblC].[cat_ID]) ORDERED FORWARD)
| |--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))
| |--Compute Scalar(DEFINE[Expr1003]=Convert([Expr1038]), [Expr1005]=If ([Expr1039]=0) then NULL else [Expr1040]))
| |--Hash Match(Aggregate, HASH[tblC].[cat_ID], [tblC].[catDesc]), RESIDUAL[tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) DEFINE[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])))
| |--Table Spool
|--Compute Scalar(DEFINE[tblC].[cat_ID]=[tblC].[cat_ID], [tblC].[catDesc]=[tblC].[catDesc]))
|--Compute Scalar(DEFINE[Expr1006]=Convert([Expr1041])))
|--Stream Aggregate(GROUP BY[tblC].[catDesc], [tblC].[cat_ID]) DEFINE[Expr1041]=COUNT_BIG([Expr1019])))
|--Sort(ORDER BY[tblC].[catDesc] ASC, [tblC].[cat_ID] ASC))
|--Hash Match(Aggregate, HASH[tblC].[cat_ID], [tblC].[catDesc], [Expr1019]), RESIDUAL([tblC].[cat_ID]=[tblC].[cat_ID] AND [tblC].[catDesc]=[tblC].[catDesc]) AND [Expr1019]=[Expr1019]))
|--Table Spool
-
looks better.
what's the change in performance?
remember to flush buffers/cache to get a proper performance benchmark.
-
SELECT
cat_ID,
catDesc,
[dbo].[CountAllSetByCatID](cat_id) AS Set_Count,
[dbo].[CountAllImgByCatID](cat_id) as Image_Count,
[dbo].[GetMinImgIDbyCatID](cat_ID) AS Image_ID,
[dbo].[GetSUMDispStatByCatIDDT](cat_ID,@d_t) AS New_Image_Count,
[dbo].[CountAllSetByCatID_DT](cat_id,@d_t) AS New_Set_Count
FROM tblCategories
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CountAllImgByCatID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CountAllImgByCatID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CountAllSetByCatID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CountAllSetByCatID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CountAllSetByCatID_DT]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[CountAllSetByCatID_DT]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMinImgIDbyCatID]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetMinImgIDbyCatID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSUMDispStatByCatIDDT]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetSUMDispStatByCatIDDT]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[CountAllImgByCatID]
(@cat_ID int)
RETURNS int
AS
BEGIN
declare @result int
select @result=count(img_ID) from tblImage where
return @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[CountAllSetByCatID]
(@cat_ID int)
RETURNS int
AS
BEGIN
declare @myReturn int
select @myReturn=count(distinct set_ID) from tblImage
where cat_ID=@cat_ID
return @myReturn
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[CountAllSetByCatID_DT]
(@cat_ID int,
@D_T datetime)
RETURNS int
AS
BEGIN
declare @myReturn int
select @myReturn=count(distinct set_ID) from tblImage
where cat_ID=@cat_ID and d_t>@D_T
return @myReturn
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[GetMinImgIDbyCatID]
(@cat_ID int)
RETURNS int
AS
BEGIN
DECLARE @result int
SELECT @result=ISNULL(MIN(img_ID),0)
FROM tblImage where cat_ID=@cat_ID
RETURN @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE FUNCTION [dbo].[GetSUMDispStatByCatIDDT]
(@cat_ID int,@d_t datetime)
RETURNS int
AS
BEGIN
declare @result int
select @result=SUM(display_status) from tblImage where cat_ID=@cat_ID and d_t > @d_t
return @result
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I assumed that display_status can be 0 or 1
try to get loose the NULL's from tables you don't need them
I just looked in the Query analizer to compare wich metod is more effective 75% old vs 25% cost this one you can try and see how it works
: )
Kindest Regards,Vasc
-
I am flushing the buffers and cache with:
DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE
Performance has not really improved. Still 3 seconds to execute
-
Can you take out some of the 'funny sh*t' and see the effect of each portion on the overall affair?
ie....build it up from a small query and see which straw breaks the camels back? Maybe you don't 'need' it all in 1.
Posting sample input data, and matching expected results may drive someone to spot a better solution.
Also what does the count(distinct()), etc do for you?
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 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
-
Re: The above example using Functions. This will never execute faster than a normal set-based approach. Using a Function in the SELECT posrtion of a query, where the function itself queries data from tables, turns your efficient set-based query into something similar to a cursor based approach, where row-based function evaluation has to occur.
-
PW you are 100% right ...
functions used like that are putting too much overhead even if the table had a few rows... the plan was way too bad : )
After another look I observed that the cost is mainly given by the DISTINCT clause... so another aproach...
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
this should work
: )
I added for tblImage index=cat_ID,set_ID
for tblCategories=skin_ID,catDesc
Kindest Regards,Vasc
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply