May 26, 2010 at 6:33 am
Hi frnds
Attached is the quey taking longer time ,can anybody suggest how to tune this query?
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 26, 2010 at 7:33 am
Here's a small speed-up which also makes the code much easier to understand:
Declare @onDate smalldatetime, @Tomorrow smalldatetime, @Yesterday smalldatetime, @CurrentMonth smalldatetime, @PrevMonth smalldatetime
select @onDate = convert(smallDateTime,convert(varchar,Month(getDate()))+'/'+convert(varchar,Day(getDate()))+'/'+convert(varchar,Year(GetDate())))
SET @Tomorrow = dateAdd(dd,1, @onDate)
SET @Yesterday = dateAdd(dd,-1, @onDate)
SET @CurrentMonth = convert(smallDateTime,convert(varchar,Month(@onDate))+'/1/'+convert(varchar,Year(@onDate)))
SET @PrevMonth = dateAdd(mm,-1, convert(smallDateTime,convert(varchar,Month(@onDate))+'/1/'+convert(varchar,Year(@onDate))))
select
count(*),
Count(distinct (case when A.Date >= @onDate and A.Date < @Tomorrow then A.LeadId end) ) as Today_Leads_Touched_base,
Count(distinct (case when A.Date >= @onDate and A.Date < @Tomorrow and OutCome = 2 then A.LeadId end) ) as Today_Leads_Converted,
Count(distinct (case when A.Date >= @onDate and A.Date < @Tomorrow and OutCome = 1 then A.LeadId end) ) as Today_Leads_Futher_Followup,
Count(distinct (case when A.Date >= @onDate and A.Date < @Tomorrow and OutCome = 3 then A.LeadId end) ) as Today_Leads_Not_Interested,
Count(distinct (case when A.Date >= @onDate and A.Date < @Tomorrow and OutCome = 4 then A.LeadId end) ) as Today_Leads_Rejected,
Count(distinct (case when A.Date >= @Yesterday and A.Date < @onDate then A.LeadId end) ) as Yesterday_Leads_Touched_base,
Count(distinct (case when A.Date >= @Yesterday and A.Date < @onDate and OutCome = 2 then A.LeadId end) ) as Yesterday_Leads_Converted,
Count(distinct (case when A.Date >= @Yesterday and A.Date < @onDate and OutCome = 1 then A.LeadId end) ) as Yesterday_Leads_Futher_Followup,
Count(distinct (case when A.Date >= @Yesterday and A.Date < @onDate and OutCome = 3 then A.LeadId end) ) as Yesterday_Leads_Not_Interested,
Count(distinct (case when A.Date >= @Yesterday and A.Date < @onDate and OutCome = 4 then A.LeadId end) ) as Yesterday_Leads_Rejected,
Count(distinct (case when A.Date >= @CurrentMonth and A.Date < @Tomorrow then A.LeadId end) ) as CurrentMonth_Leads_Touched_base,
Count(distinct (case when A.Date >= @CurrentMonth and A.Date < @Tomorrow and OutCome = 2 then A.LeadId end) ) as CurrentMonth_Leads_Converted,
Count(distinct (case when A.Date >= @CurrentMonth and A.Date < @Tomorrow and OutCome = 1 then A.LeadId end) ) as CurrentMonth_Leads_Futher_Followup,
Count(distinct (case when A.Date >= @CurrentMonth and A.Date < @Tomorrow and OutCome = 3 then A.LeadId end) ) as CurrentMonth_Leads_Not_Interested,
Count(distinct (case when A.Date >= @CurrentMonth and A.Date < @Tomorrow and OutCome = 4 then A.LeadId end) ) as CurrentMonth_Leads_Rejected,
Count(distinct (case when A.Date >= @PrevMonth and A.Date < @CurrentMonth then A.LeadId end) ) as PrevMonth_Leads_Touched_base,
Count(distinct (case when A.Date >= @PrevMonth and A.Date < @CurrentMonth and OutCome = 2 then A.LeadId end) ) as PrevMonth_Leads_Converted,
Count(distinct (case when A.Date >= @PrevMonth and A.Date < @CurrentMonth and OutCome = 1 then A.LeadId end) ) as PrevMonth_Leads_Futher_Followup,
Count(distinct (case when A.Date >= @PrevMonth and A.Date < @CurrentMonth and OutCome = 3 then A.LeadId end) ) as PrevMonth_Leads_Not_Interested,
Count(distinct (case when A.Date >= @PrevMonth and A.Date < @CurrentMonth and OutCome = 4 then A.LeadId end) ) as PrevMonth_Leads_Rejected
from LMS_LeadFollowup A
inner join
(
select A.emp_code
from TBL_EMP_MST A
inner join (
select emp_code, LeftExtent, RightExtent
from TBL_EMP_MST
where emp_code = 17
) B on A.LeftExtent >= B.LeftExtent and A.RightExtent <= B.RightExtent
) C on A.FollowupBy = C.emp_code
inner join LMS_LeadMaster X on A.LeadId = X.LeadId
-- why is this here?
left outer join TBL_EMP_MST D on C.emp_code = D.emp_code
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 26, 2010 at 7:40 am
Few findings
1. There is no sargable clause/filter which leads to SCAN.
2.
select A.emp_code
from TBL_EMP_MST A
inner join (
select emp_code, LeftExtent, RightExtent
from TBL_EMP_MST
where emp_code = 17
) B on A.LeftExtent >= B.LeftExtent and A.RightExtent <= B.RightExtent
try to keep it in temp table , place an index and then use it with
inner join LMS_LeadMaster X on A.LeadId = X.LeadId
left outer join TBL_EMP_MST D on C.emp_code = D.emp_code
3. and most important data is very huge which also using table spooling
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 26, 2010 at 7:48 am
Aggregating twice may help significantly:
SELECT
Period,
OutCome,
SUM(LeadCount) -- sum rowcounts
FROM (
SELECT
A.Date,
Period = CASE
WHEN A.Date >= @PrevMonth AND A.Date < @CurrentMonth THEN 'PrevMonth'
WHEN A.Date >= @CurrentMonth AND A.Date < @Tomorrow THEN 'CurrentMonth'
WHEN A.Date >= @Yesterday AND A.Date < @onDate THEN 'Yesterday'
WHEN A.Date >= @onDate AND A.Date < @Tomorrow THEN 'Today'
END,
A.OutCome,
LeadCount = COUNT(A.LeadId) -- count rows
FROM LMS_LeadFollowup A
INNER JOIN LMS_LeadMaster X on A.LeadId = X.LeadId
INNER JOIN
(
select A.emp_code
from TBL_EMP_MST A
inner join (
select emp_code, LeftExtent, RightExtent
from TBL_EMP_MST
where emp_code = 17
) B on A.LeftExtent >= B.LeftExtent and A.RightExtent <= B.RightExtent
) C on A.FollowupBy = C.emp_code
GROUP BY A.Date, A.OutCome
) d
GROUP BY Period, OutCome
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
May 27, 2010 at 9:36 pm
thanx a lot friends
it worked
Sanket Ahir
Don't run behind the success, Try to be eligible & success will run behind u......
May 28, 2010 at 1:21 am
sanketahir1985 (5/27/2010)
it worked
what changes have you made ? please share with us
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply