May 11, 2010 at 6:39 pm
Hello,
I’m looking for some performance suggestions with a report query that when run in query analyzer takes almost 20 minutes to complete. Here is a rough break down of the information that is being worked on.
•2000 entries are submitted to a competition.
•Each entry is assigned 10 judges.
•Each judge sets a score from 1-100 on 3 different judging criteria.
•Demographic data about the user and competition
The report is just a summation of all the entries including scoring averages, user demographic information, competition information, etc. Pretty much just a summary of the competition after it is completed. 2000 entries doesn’t sounds like a lot, but the sub query to calculate the averages alone returns about 60,000 records, 2000 entries X 10 judges X 3 judging criteria.
I’m a .Net web developer by trade so I have above average SQL knowledge, but where I am lacking is writing SQL for performance, table creation for performance, DB tuning for performance. I can retrieve the data for the client, but when the records/complexity start increasing the performance degrades quickly.
In general when you are dealing with high record counts or intense calculations what does one need to do from infrastructure to code to keep things moving quickly. I know there is much more complex reports being run out there in a web environment and I can’t image people enjoy web pages timing out due to poor query performance (yes I do extend the page timeout values).
I know there is a wealth of information out there, but the resources I have to read up on these topics are limited so I’m hoping for some feedback on topics/resources that can best optimize my time spent learning with results for my clients.
I’ve included the SQL code for the report below along with the function that the query utilizes to calculate the average scores per entry.
Thanks.
-Bryan
select T.Category_Code as [Category Number], T.Category_Name as [Category Title], T.Entry_ID as [Entry ID],
T.Entry_Title as [Entry Title], T.Company,
T.MarketingObjective as [Marketing Objective], T.Competition_Code as Competition,
T.Profile_FirstName as [Contact First Name], T.Profile_LastName as [Contact Last Name],
T.Profile_Address1 as [Contact Address Line 1], T.Profile_Address2 as [Contact Address Line 2],
T.Profile_City as [Contact City], T.Profile_StateProvince as [Contact State], T.Profile_ZipCode as [Contact Zip],
T.Profile_Country as [Contact Country], T.Profile_Company as [Contact Company],
T.Profile_Email as [Contact Email], T.Profile_Phone as [Contact Phone], T.Group_ID, T.Group_Name, TSCL.FinalScore as [FinalScore], TYN.YesNo as [YesNo], T.Place, T.Reach_Item as [Reach Code],
T.SubmsCount + isnull(TC.VideoSpots,0) as [No received per entry] from
(select CCS.Category_Code, CCS.Category_Name, E.Entry_ID,
E.Entry_Title,
case when Ltrim(Rtrim(isnull(E.SubmitCompany,'')))='' then 'COMPANY NOT ENTERED' else E.SubmitCompany end as Company,
E.MarketingObjective, C.Competition_Code,
case when HL.MemberKey is null then UP.Profile_FirstName else HL.FirstName end as Profile_FirstName,
case when HL.MemberKey is null then UP.Profile_LastName else HL.LastName end as Profile_LastName,
case when HL.MemberKey is null then UP.Profile_Address1 else HL.Address1 end as Profile_Address1,
case when HL.MemberKey is null then UP.Profile_Address2 else HL.Address2 end as Profile_Address2,
case when HL.MemberKey is null then UP.Profile_City else HL.City end as Profile_City,
case when HL.MemberKey is null then UP.Profile_StateProvince else HL.StateProvinceCode end as Profile_StateProvince,
case when HL.MemberKey is null then UP.Profile_ZipCode else HL.PostalCode end as Profile_ZipCode,
case when HL.MemberKey is null then UP.Profile_Country else HL.CountryCode end as Profile_Country,
case when HL.MemberKey is null then UP.Profile_Company else HL.CompanyName end as Profile_Company,
case when HL.MemberKey is null then UP.Profile_Email else HL.EmailAddress end as Profile_Email,
case when HL.MemberKey is null then UP.Profile_Phone else HL.Phone1 end as Profile_Phone, JG.Group_ID, JG.Group_Name,
W.Place, isnull(RC.Reach_Item,'') as Reach_Item,
count(S.Video_Path) + count(S.DateReceived) as SubmsCount
from AW_Entry E
left join AW_Category_CompSpec CCS on E.Category_ID=CCS.Category_ID
left join AW_Competition_CategoryCompSpec ComCCS on CCS.Category_ID=ComCCS.Category_ID
left join AW_Competition C on C.Competition_ID=ComCCS.Competition_ID
left join AW_UserProfile UP on E.Usr_ID=UP.Profile_ID
left join HLMember HL on HL.MemberKey=E.Usr_ID
left join AW_ReachCodes RC on E.ReachCode_ID=RC.Reach_ID
left join AW_Submission S on E.Entry_ID=S.Entry_ID
left join AW_MediaType MT on S.MediaType_ID=MT.MediaType_ID
join AW_JudgingGroup_Entry JGE on E.Entry_ID=JGE.Entry_ID
join AW_JudgingGroups JG on JGE.Group_ID=JG.Group_ID
join AW_JudgingSessions JS on JG.Session_ID=JS.Session_ID and JS.JudLevel=@JudLevel
left join AW_Winners W on E.Entry_ID=W.Entry_ID and W.JudLevel=@JudLevel
where E.SaveStatus='LIVE' and E.EntryStatus_ID>=3 and C.Competition_ID=@CompID
group by
CCS.Category_Code, CCS.Category_Name, E.Entry_ID,
E.Entry_Title, E.SubmitCompany, E.MarketingObjective, C.Competition_Code,
UP.Profile_FirstName, UP.Profile_LastName, UP.Profile_Address1, UP.Profile_Address2,
UP.Profile_City, UP.Profile_StateProvince, UP.Profile_ZipCode, UP.Profile_Country, UP.Profile_Company,
UP.Profile_Email, UP.Profile_Phone, JG.Group_ID, JG.Group_Name,
HL.MemberKey, HL.FirstName, HL.LastName, HL.Address1, HL.Address2, HL.City, HL.StateProvinceCode,
HL.PostalCode, HL.CountryCode, HL.CompanyName, HL.EmailAddress, HL.Phone1,
W.Place, RC.Reach_Item) T
left join AW_TimeCodes TC on T.Entry_ID=TC.Entry_ID and TC.Video_Path is not NULL
left join
(select Entry_ID, convert(decimal(18,2),avg(ScorePerJudge)) as FinalScore
from (select Entry_ID, GroupJudge_ID,
convert(decimal(18,2),avg(convert(decimal(18,2),Score))) as ScorePerJudge
from fn_GetFinalScores (@CompID, @JudLevel)
group by Entry_ID, GroupJudge_ID) TINT group by Entry_ID) TSCL on T.Entry_ID=TSCL.Entry_ID
left join
(select D.Entry_ID, convert(varchar(50),D.YesFinCount) + '/' + convert(varchar(50),D.NoFinCount) as YesNo
from (select Entry_ID, count(YesOption) as YesFinCount, count(NoOption) as NoFinCount
from (select Entry_ID, GroupJudge_ID,
case when Option_Value=1 then 1 else NULL end as YesOption,
case when Option_Value=0 then 1 else NULL end as NoOption
from fn_GetFinalScores (@CompID, @JudLevel)) TINNR
group by Entry_ID) D ) TYN on T.Entry_ID=TYN.Entry_ID
order by T.Category_Name, T.Entry_ID
ALTER FUNCTION [dbo].[fn_GetFinalScores] (@CompID bigint, @JudLevel int)
RETURNS
@FinalScores TABLE
(
Entry_ID bigint,
Category_ID bigint,
Category_Name varchar(100),
Category varchar(300),
EntryName varchar(200),
Submitter varchar(500),
Score_ID bigint,
Criteria_ID bigint,
Score int,
Option_ID int,
Option_Name varchar(50),
Option_Value bit,
GroupEntry_ID bigint,
GroupJudge_ID bigint
)
AS
BEGIN
declare @ScoreTypeID int
select top 1 @ScoreTypeID=ScoreType_ID from AW_ScoreCriteria where Session_ID in
(select Session_ID from AW_JudgingSessions where Competition_ID=@CompID and JudLevel=@JudLevel)
insert into @FinalScores
select distinct T.Entry_ID, T.Category_ID, T.Category_Name, T.Category, T.EntryName, T.Submitter,
SC.Score_ID, SC.Criteria_ID, SC.Score, SC.Option_ID, SO.Option_Name, SO.Option_Value,
SC.GroupEntry_ID, SC.GroupJudge_ID
from
(select E.Entry_ID, E.Category_ID, CCS.Category_Name,
Convert(VarChar,CCS.Category_Code) + ' - ' + Convert(VarChar,CCS.Category_ID_BDA) + ' - ' + CCS.Category_Name As Category,
convert(VarChar,E.Entry_ID) + ' - ' + E.Entry_Title as EntryName,
case when Ltrim(Rtrim(isnull(E.SubmitCompany,'')))='' then 'COMPANY NOT ENTERED' else E.SubmitCompany end + ', ' + case when UP.Profile_LastName is not null then UP.Profile_FirstName + ' ' + UP.Profile_LastName else case when HL.LastName is not null then HL.FirstName + ' ' + HL.LastName else '' end end as Submitter
from AW_Entry E
left join AW_Category_CompSpec CCS on E.Category_ID=CCS.Category_ID
left join AW_Competition_CategoryCompSpec ComCCS on CCS.Category_ID=ComCCS.Category_ID
left join AW_Competition C on C.Competition_ID=ComCCS.Competition_ID
left join AW_UserProfile UP on E.Usr_ID=UP.Profile_ID
left join HLMember HL on E.Usr_ID=HL.MemberKey
where E.SaveStatus='LIVE' and E.EntryStatus_ID>=3 and C.Competition_ID=@CompID) T
join AW_JudgingGroup_Entry JGE on T.Entry_ID=JGE.Entry_ID
join AW_JudgingGroups JG on JGE.Group_ID=JG.Group_ID
join AW_JudgingSessions JS on JG.Session_ID=JS.Session_ID and JS.JudLevel=@JudLevel
join AW_ScoreCriteria SCCR on JS.Session_ID=SCCR.Session_ID and SCCR.ScoreType_ID=@ScoreTypeID
join AW_Scores SC on JGE.GroupEntry_ID=SC.GroupEntry_ID and SCCR.Criteria_ID=SC.Criteria_ID
left join AW_ScoreOptions SO on SC.Option_ID=SO.Option_ID
where SC.DisqCode_ID is NULL and SC.AbstCode_ID is NULL
order by Category_Name, Entry_ID
RETURN
END
May 11, 2010 at 7:53 pm
The problems are many here. You're simply trying to do too much in a single GROUP BY which is why you have to duplicate so much of the SELECT list in the GROUP BY. In order to make this run much faster, do all of the aggregations FIRST in a CTE, Derived Table, or maybe even with a Temp Table. Then worry about how to display it with names and all.
Just like in programming where you keep the presentation, business, and data layers separate from one another, so must you do with this query. Divide'n'Conquer... solve the math first leaving a joinable ID exposed and then present it.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2010 at 8:24 pm
Thank you for the response Jeff. Your reply along with the two linked posts in your signature set me on a path that helped me take the query from a run time of 19:30 down to :28. Along the way I picked up some new tricks and have a whole new mindset when tackling SQL queries.
Thanks again.
-Bryan
May 12, 2010 at 10:33 pm
bbeaumont (5/12/2010)
Thank you for the response Jeff. Your reply along with the two linked posts in your signature set me on a path that helped me take the query from a run time of 19:30 down to :28. Along the way I picked up some new tricks and have a whole new mindset when tackling SQL queries.Thanks again.
-Bryan
Absolutely outstanding job on your part, Bryan! 40 times faster is a heck of an accomplishment. Well done! Any chance that you could post your modified code so that others can see both the Before'n'After? I'm thinking that it'll provide a bit of inspiration. I'm also don't know if your of the ilk, but it would make a hell of an article for this site if you were able to chronicle what you did to wittle down the problem. Heh... it's not something you can retire on but they DO pay you for articles on this site.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2010 at 8:36 am
I would certainly be interested in a before/after solution. The fact that you took Jeff's response and solved the problem yourself is quite a testament to your tenacity.
And, if you do write an article on this, I would model it on any of Jeff's articles. He's one of the best.
Good work,
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply