January 24, 2011 at 2:33 pm
Hey there everyone
I have a store procedure, originally this procedure was taking around 3 min to return the result set ive made several changes to it and now im getting the same result set in 10 - 15 secs but i think this is still slow, so i really dont know what im missing, im sending a zip file with the tables structured involved, also ive included de execution plan and the indexes.
this is the store procedure, or well is the section is causing me troubles.....
DECLARE@StartDate datetime,@EndDate datetime,@RTSelection int,@LeadSource nvarchar(255)
DECLARE@SubLeadSource nvarchar(255),@employeeid int,@AffinityGroup nvarchar(255)
SET @StartDate='08/23/2005 12:00:00 AM'
SET @EndDate='12/31/2010 12:00:00 AM'
SET @RTSelection='900'
SET @LeadSource='%'
SET @SubLeadSource=''
SET @employeeid='9999'
SET @AffinityGroup='-2'
IF @AffinityGroup = '-2'
BEGIN
SELECT
RTRIM(E.LastName) + ', ' + SUBSTRING (E.FirstName, 1 , 1) + '.' AS "Employee name",
SUM(Case A.ActivityTypeCode When '1' Then 1 Else 0 End) as Appels,
SUM(Case WHEN A.ActivityTypeCode = '1' AND A.ActivityResult = '1' Then 1 Else 0 End) as "Contacts",
SUM(Case A.ActivityTypeCode When '2' Then 1 Else 0 End) as r_v,
SUM(Case WHEN A.ActivityTypeCode = '2' AND (A.ActivityResult = '1' or A.ActivityResult = '2' OR A.ActivityResult = '3' OR A.ActivityResult = '9') Then 1 Else 0 End) as Pres,
SUM(Case When A.ActivityResult = '3' THEN (case when ISNULL(L.SProductVol,0) > 0 THEN 1 ELSE 0 END +
case when ISNULL(L.AEProductVol,0) > 0 THEN 1 ELSE 0 END + case when ISNULL(L.FFProductVol,0) > 0 THEN 1 ELSE 0 END ) Else 0 END) as Contracts,
SUM(Case WHEN A.ActivityResult = '3' Then 1 Else 0 End) as Ventes,
SUM(Case WHEN A.ActivityResult = '3' THEN ISNULL(L.SProductVol,0) ELSE 0 END) as TotalSProduct,
SUM(Case WHEN A.ActivityResult = '3' THEN ISNULL(L.AEProductVol,0) ELSE 0 END) as TotalAEProduct,
SUM(Case WHEN (A.ActivityResult = '3' AND ISNULL(L.LeadSource,0)!=7) THEN ISNULL(L.FFProductVol,0) ELSE 0 END) as TotalFFProduct,
SUM(Case WHEN A.ActivityResult = '3' THEN ISNULL(L.SProductVol,0) +
ISNULL(L.AEProductVol,0) + ISNULL(L.FFProductVol,0) ELSE 0 END) as NetTotal,
(select SUM(Case WHEN L1.ActivityTypeCode = '2' AND A1.ActivityTypeCode = '0' AND
DATEADD(day,-7,L1.ScheduledStart) Between @StartDate And DATEADD(day,1,@EndDate) THEN 1 ELSE 0 END)from lead l1
inner join activitybase a1 on a1.idLead = l1.ID RIGHT JOIN [Employees] E1 ON L1.employeePK = E1.employeePK where e.employeePK = E1.employeePK GROUP BY E1.employeePK) as RVSP,
SUM(Case When (A.ActivityResult = '3' AND ISNULL(L.FFProductVol,0) > 0 AND ISNULL(L.LeadSource,0)!=7)
THEN 1 ELSE 0 END) as FFContracts,
SUM(CASE WHEN A.ActivityResult = '3' THEN
CASE L.SCelebris WHEN 1 THEN 1 ELSE 0 END +
CASE L.AECelebris WHEN 1 THEN 1 ELSE 0 END +
CASE L.FFCelebris WHEN 1 THEN 1 ELSE 0 END ELSE 0 END) as CelebrisContracts,
SUM(CASE WHEN A.ActivityResult = '-1' THEN 1 ELSE 0 END ) as LeadCounter,
SUM(Case When (A.ActivityResult = '3' AND ISNULL(L.SProductVol,0) > 0)
THEN 1 ELSE 0 END) as SContracts,
SUM(Case When (A.ActivityResult = '3' AND ISNULL(L.AEProductVol,0) > 0)
THEN 1 ELSE 0 END) as AEContracts,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjAppels END) as "ObjAppels",
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjContacts END) as "ObjContacts",
MIN(CASE WHEN E.RoleID = 3 THEN E.Objr_v END) as Objr_v, MIN(E.ObjPres) as ObjPres,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjContracts END) as ObjContracts, MIN(E.ObjVentes) as ObjVente,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjTotalSProduct END) as ObjTotalSProduct,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjTotalAEProduct END) as ObjTotalAEProduct,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjTotalFFProduct END) as ObjTotalFFProduct,
MIN(CASE WHEN E.RoleID = 3 THEN E.ObjNetTotal END) as ObjNetTotal,
E.Guid AS "EmployeeGuid"
FROM Lead L
INNER JOIN [ActivityBase] A ON L.ID = A.idLead
INNER JOIN [Employees] E ON L.employeePK = E.employeePK
INNER JOIN ([Territories] T
INNER JOIN ([Region] R
INNER JOIN regionACL ra ON ((ra.RegionId = R.RegionId) AND (ra.employeeid = @employeeid)))
ON T.RegionID = R.RegionID)
ON L.TerritoryID = T.TerritoryID
WHERE ((@RTSelection = 900) OR (T.RegionID = (@RTSelection - 900 ))) AND ( @LeadSource='%' or L.LeadSource LIKE @LeadSource) AND ( @SubLeadSource='' or @SubLeadSource='%' or L.SubLeadSource Like @SubLeadSource)
AND A.Createdon Between @StartDate And DATEADD(day,1,@EndDate)
GROUP BY E.Guid, E.LastName, E.FirstName,E.employeePK
ORDER BY E.LastName, E.FirstName
END
The activityBase table is the big one it has around 1 mill rows, the lead table hast around 100k rows and the other tables have a couple of thousand rows.....
According to the execution plan the time consuming sections is a Index Seek on createdOn and a Hash Match.
The thing is i dont know how to get rid of them..
Any help will be appreciated.
Edit: Forgot to add the files....here they are:
January 24, 2011 at 3:06 pm
Having the table definitions would help, but some of your logic seems a bit odd.
Based on the way your query is set up, this WHERE Clause is always true: ((@RTSelection = 900) OR (T.RegionID = (@RTSelection - 900 ))) - Since you set @RTSelection = 900 when you initialise your variables. This is probably returning a lot more rows than you actually want since it isn't only returning rows where T.RegionID = (@RTSelection - 900).
You will see your query returns the same result set with or without the section T.RegionID = (@RTSelection - 900 ). This applies to other sections of your query.
I'd fix this up first, then I'd tidy up the joins and move the condition AND (ra.employeeid = @employeeid) out of the join.
Doing this may make it easier to read and removing the odd logic may result in a smaller record set.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
January 24, 2011 at 7:44 pm
Also, you can greatly reduce the number of ISNULL calculations simply because you cannot compare against a NULL. For example, the following snippet from your code...
case when ISNULL(L.AEProductVol,0) > 0 THEN 1 ELSE 0 END
... can be simplified a bit thanks to the lovely nature of NULLs like the following...
case when L.AEProductVol > 0 THEN 1 ELSE 0 END
Both pieces of code will return the same result even in the presence of NULLs. Similar areas in your code could be simplified, as well.
So far as speeding up the code goes, perhaps dividing the problem up as a smaller temp table to provide the lookup against the much larger table would do you well. I've converted many a long running report (30+minutes) to run in mere seconds (<3) using "Divide'n'Conquer" methods. I'm not guaranteeing they'd work here (because I can't play with your data), but it may be worth a try on your part.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 24, 2011 at 9:00 pm
Well those values there are just an example.......those variable are parameters, they get their values from combo boxes and text fields, i mean those values are set by user selections......i just left those values there cuz im testing the query thats it.
January 25, 2011 at 6:37 am
You asked for suggested performance improvements and I can only go by the code you posted. Removing the ISNULLs in the fashion I suggested and for the reason I suggested won't turn your query into a miracle of performance but it will help.
As I also suggested, dividing the problem up into smaller queries will likely give you the performance you seek.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2011 at 7:13 am
Ok thks jeff for the suggestion ill try that today, anyway i forgot to upload the files yesterday, i've just done it....its a link because for some reason i wasnt be able to upload them, i kept gettin some Aspx error.
Inside the file are the table and indexes definitions and the execution plan file.
January 30, 2011 at 6:40 pm
joelvillegas (1/25/2011)
Ok thks jeff for the suggestion ill try that today, anyway i forgot to upload the files yesterday, i've just done it....its a link because for some reason i wasnt be able to upload them, i kept gettin some Aspx error.Inside the file are the table and indexes definitions and the execution plan file.
Sorry, lost track of this one. I can't tell what's actually going on because you've only provided and estimated execution plan, but it would appear that the following subquery is taking more than 75% of the estimated batch time...
(select SUM(Case WHEN L1.ActivityTypeCode = '2' AND A1.ActivityTypeCode = '0' AND
DATEADD(day,-7,L1.ScheduledStart) Between @StartDate And DATEADD(day,1,@EndDate) THEN 1 ELSE 0 END)from lead l1
inner join activitybase a1 on a1.idLead = l1.ID RIGHT JOIN [Employees] E1 ON L1.employeePK = E1.employeePK where e.employeePK = E1.employeePK GROUP BY E1.employeePK) as RVSP,
That would appear to be a GROUP BY within a GROUP BY (the outer query this was in is a GROUP BY). My recommendation stands... Divide'n'Conquer. Writing set-based could doesn't mean writing it all in on huge query. Solve all the probems except for the sub-query above and store them in a table. Then solve the above subquery.
I'm not there and I can't tell 100% from here, but I believe that's where I'd start.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 31, 2011 at 7:52 am
I say the best way to handle this is to use dynamic sql to construct queries that have explicit values coded for those variables and more importantly allow you to REMOVE unnecessary stuff like some ORs and complex ANDs.
Jeff's notions towards breaking the problem down into smaller components is a likely winner too if dynamic sql doesn't get it fast enough.
Oh, and sometimes OR clauses can be broken down into separate UNION (or better - UNION ALL) SELECTs to get MUCH better performance.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply