July 29, 2011 at 11:19 am
Hi Guru,
I have EXACTLY the same two queries one is Adhoc and one is wrapped inside a stored procedure. They both return the same exact results but the adhoc runs 3 times faster than procedure with exact same one set of parameters. Below are my very interesting findings:
1. Adhoc uses Parallelisum plan but procedure does not.
2. Adhoc does not complain missing index but procedure does.
3. Adhoc generates 4.3 millions reads but proc only 1.3 million reads
I can confirm no parameter sniffings, statistics are up-to-date and both of them use existing plans in the procedure cache (no recompile). Even I created brand new proc on different database and it still runs the same.
What is the real issue here? Could it be user defined functions when invoking from a proc?
Thanks so much.
Attopeu,
declare @StartTime DateTime='07/08/2011 19:00PM',
@EndTime DateTime,
@StartTimeUTC datetime,
@EndTimeUTC dateTime,
@radid int
--set @StartTime ='07/08/2011 19:00PM'
--declare @StartTimeUTC datetime, @EndTimeUTC dateTime
if @StartTime is null
begin
set @StartTime = GETDATE()-1
end
if @EndTime is null
begin
set @EndTime = GETDATE()
end
set @EndTime = GETDATE()
set @StartTimeUTC = (select DateTimeUtc from udf_ConvertCtToUtc(@StartTime))
set @EndTimeUTC = (select DateTimeUtc from udf_ConvertCtToUtc(@EndTime));
set @radid = 3702;
with
transaction_cte as
(
select
it.DoctorID
,it.DateDistributed
,it.DatePicked
,it.OrderID
,it.TransID
,CA1.DateTimeUtc as DatePickedUTC
,CA2.DateTimeUtc as DateDistributedUTC
from
invoice_tblTransactions it
cross apply udf_ConvertCtToUtc (it.datePicked) as CA1
cross apply udf_ConvertCtToUtc (it.DateDistributed) as CA2
where
it.DatePicked <= @EndTime
AND it.DateDistributed >= @StartTime
And (@radid is null OR @radid = it.DoctorID)
)
,Schedule_cte as
(
select
sch.RadID
,sch.ScheduleID
,ShiftTypeID
,sch.Remarks
,sch.StartDateTimeUTC
,sch.EndDateTimeUTC
from Schedule.Schedule sch
where sch.StartDateTimeUTC >= @StartTimeUTC
AND sch.EndDateTimeUTC <= @EndTimeUTC
and sch.ShiftTypeID in (
10,-- extra shift
20,-- shift swap
60,-- pay it back
70,-- pay it forward
80,-- surge extra shift
90,-- change time
99,-- schedule
103,-- non-Contracted hours
100)-- UCH
and ScheduleTypeID = 2
And (@radid is null OR @radid = sch.RadID)
)
,SingleShiftStuff
as
(
select RadID, max(lu.ShiftTypeName) as ShiftTypeID, min(sch.StartDateTimeUTC) as FirstStartUTC, max(sch.EndDateTimeUTC)as LastEndUTC, max(sch.Remarks) as comments, SUM(datediff(MINUTE,sch.StartDateTimeUTC,sch.EndDateTimeUTC)) as ShiftMin
from
Schedule_cte sch
inner join Schedule.LuShiftType lu
on sch.ShiftTypeID = lu.ShiftTypeID
group by RadID
)
, schedule_counts
as
(
select RadID, COUNT(ScheduleID) as ScheduledTimes
from Schedule_cte
group by RadID
)
,ScheduledTransactions_cte as
(
select t.TransID, t.DoctorID as RadID, t.DatePicked, t.DateDistributed, 1 as Scheduled
from
transaction_cte t
cross Apply( select top(1) 1 as sch
from Schedule_cte sch
where
t.DoctorID = sch.RadID
and
(
(t.DatePickedUTC <= sch.EndDateTimeUTC and t.DatePickedUTC >= sch.StartDateTimeUTC)
OR (t.DateDistributedUTC <= sch.EndDateTimeUTC and t.DateDistributedUTC >= sch.StartDateTimeUTC)
)
) CA1
)
,startFringe_cte as
(
select t.TransID, t.DoctorID as RadID, t.DatePicked, t.DateDistributed, 1 as StartFringe
from
transaction_cte t
cross Apply( select top(1) 1 as sch
from Schedule_cte sch
where
t.DoctorID = sch.RadID
and
(
(t.DatePickedUTC < sch.StartDateTimeUTC AND (t.DateDistributedUTC <= sch.EndDateTimeUTC and t.DateDistributedUTC >= sch.StartDateTimeUTC))
)
) CA1
)
,EndFringe_cte as
(
select t.TransID, t.DoctorID as RadID, t.DatePicked, t.DateDistributed, 1 as EndFringe
from
transaction_cte t
cross Apply( select top(1) 1 as sch
from Schedule_cte sch
where
t.DoctorID = sch.RadID
and
(
(t.DateDistributedUTC > sch.EndDateTimeUTC AND (t.DatePickedUTC <= sch.EndDateTimeUTC and t.DatePickedUTC >= sch.StartDateTimeUTC))
)
) CA1
)
,fringe_count
as(
select fringe.RadID, SUM(fringe.StartFringe) StartFringeCount, SUM(fringe.EndFringe) EndFringeCount
from
(
select fs.TransID, fs.RadID, fs.StartFringe, 0 as EndFringe from startFringe_cte fs
union ALL
select fe.TransID, fe.RadID, 0 as StartFringe, EndFringe from EndFringe_cte fe
) as fringe
group by RadID
)
,SingleShiftTransactionStuff
as
(
select RadID, MIN(ScheduledTransactions_cte.DatePicked)as FirstOpened, MAX(ScheduledTransactions_cte.DateDistributed)as LastSigned
from ScheduledTransactions_cte
group by RadID
)
,UnScheduledTransactions_cte as
(
select t.TransID, t.DoctorID as RadID, 1 as UnScheduled
from transaction_cte t
where t.TransID not in (select TransID from ScheduledTransactions_cte)
)
,EmptyTime_cte
as
(
select rut.RadID, sum(ISNULL(DATEDIFF(MINUTE,rut.BeginDateUTC, rut.EndDateUTC),0)) as minEmpty
from dbo.RadUtilTracking rut
inner join Schedule_cte sch
on rut.RadID = sch.RadID
where rut.BeginDateUTC >= sch.StartDateTimeUTC and rut.EndDateUTC<=sch.EndDateTimeUTC
and rut.RadUtilType = 'EMPTY '
group by rut.RadID
)
,IdleTime_cte
as
(
select rut.RadID, sum(ISNULL(DATEDIFF(MINUTE,rut.BeginDateUTC, rut.EndDateUTC),0)) as minIdle
from dbo.RadUtilTracking rut
inner join Schedule_cte sch
on rut.RadID = sch.RadID
where rut.BeginDateUTC >= sch.StartDateTimeUTC and rut.EndDateUTC<=sch.EndDateTimeUTC
and rut.RadUtilType = 'IDLE '
group by rut.RadID
)
,TransactionCounts
as(
select
radid
,count(transactions.TransID) as CasesRead
,SUM(Scheduled) as ScheduledReads
,SUM(UnScheduled) as UnScheduledReads
from(
select sch.TransID, RadID, Scheduled, 0 as Unscheduled from ScheduledTransactions_cte sch
union ALL
select UnSch.TransID, RadID, 0 as Scheduled, UnScheduled from UnScheduledTransactions_cte UnSch
)as transactions
group by RadID
)
select
rad.RadID,
rad.LastCommaFirst
,ISNULL(schedule_counts.ScheduledTimes,0) as ScheduledTimes
,CasesRead
,ScheduledReads
,UnScheduledReads
,isnull(minEmpty,0) as 'Empty(Min)'
,ISNULL(minIdle,0) as 'Idle(Min)'
,(case when ISNULL(schedule_counts.ScheduledTimes,0)=0 then 'N/A' when 1=1 then cast(ISNULL(minIdle,0)/ ISNULL(schedule_counts.ScheduledTimes,0) as varchar(10)) end) as 'AvgIdlePerShift(Min)'
,isnull(ShiftMin,0) as 'Scheduled(Min)'
,isnull(ShiftMin,0) - isnull(minEmpty,0) - ISNULL(minIdle,0) as 'CalculatedReading(Min)'
,isnull(cast(sst.FirstOpened AS varchar(50)),'N/A') as FirstOpen
,case when schedule_counts.ScheduledTimes = 1 then cast((select DateTimect from udf_ConvertUTCTocT(sss.FirstStartUTC)) AS varchar(50)) when 1=1 then 'N/A' end as ShiftStart
,case when schedule_counts.ScheduledTimes = 1 then cast((select DateTimect from udf_ConvertUTCTocT(sss.LastEndUTC)) AS varchar(50)) when 1=1 then 'N/A' end as ShiftEnd
,isnull(cast(sst.LastSigned AS varchar(50)),'N/A') as LastSigned
,case when schedule_counts.ScheduledTimes = 1 then isnull(sss.comments, '') when 1=1 then 'N/A' end as comments
,case when schedule_counts.ScheduledTimes = 1 then isnull(sss.ShiftTypeID, '') when 1=1 then 'N/A' end as shiftType
,ISNULL(fringe_count.StartFringeCount,0) as StartFringeCount
,ISNULL(fringe_count.EndFringeCount,0) as EndFringeCount
from
TransactionCounts
INNER JOIN Rad.Rad as rad
on rad.RadID = TransactionCounts.RadID
LEFT OUTER JOIN schedule_counts
on rad.RadID = schedule_counts.RadID
LEFT OUTER JOIN EmptyTime_cte
on rad.RadID = EmptyTime_cte.RadID
LEFT OUTER JOIN IdleTime_cte
on rad.RadID = IdleTime_cte.RadID
LEFT OUTER JOIN SingleShiftTransactionStuff sst
on rad.RadID = sst.RadID
LEFT OUTER JOIN SingleShiftStuff sss
ON rad.RadID = sss.RadID
LEFT OUTER JOIN fringe_count
ON rad.RadID = fringe_count.RadID
order by
LastCommaFirst
July 29, 2011 at 11:22 am
Please post both actual execution plans so we figure the best way out of the parameter sniffing problem (yes that's the issue).
July 29, 2011 at 11:24 am
A little easier on the eyes...
declare
@StartTime DateTime= '07/08/2011 19:00PM'
, @EndTime DateTime
, @StartTimeUTC datetime
, @EndTimeUTC dateTime
, @radid int
--set @StartTime ='07/08/2011 19:00PM'
--declare @StartTimeUTC datetime, @EndTimeUTC dateTime
if @StartTime is null
begin
set @StartTime = GETDATE() - 1
end
if @EndTime is null
begin
set @EndTime = GETDATE()
end
set @EndTime = GETDATE()
set @StartTimeUTC = (
select
DateTimeUtc
from
udf_ConvertCtToUtc(@StartTime)
)
set @EndTimeUTC = (
select
DateTimeUtc
from
udf_ConvertCtToUtc(@EndTime)
) ;
set @radid = 3702 ;
with transaction_cte
as (
select
it.DoctorID
, it.DateDistributed
, it.DatePicked
, it.OrderID
, it.TransID
, CA1.DateTimeUtc as DatePickedUTC
, CA2.DateTimeUtc as DateDistributedUTC
from
invoice_tblTransactions it
cross apply udf_ConvertCtToUtc(it.datePicked) as CA1
cross apply udf_ConvertCtToUtc(it.DateDistributed) as CA2
where
it.DatePicked <= @EndTime
AND it.DateDistributed >= @StartTime
And (
@radid is null
OR @radid = it.DoctorID
)
) ,
Schedule_cte
as (
select
sch.RadID
, sch.ScheduleID
, ShiftTypeID
, sch.Remarks
, sch.StartDateTimeUTC
, sch.EndDateTimeUTC
from
Schedule.Schedule sch
where
sch.StartDateTimeUTC >= @StartTimeUTC
AND sch.EndDateTimeUTC <= @EndTimeUTC
and sch.ShiftTypeID in ( 10 ,-- extra shift
20 ,-- shift swap
60 ,-- pay it back
70 ,-- pay it forward
80 ,-- surge extra shift
90 ,-- change time
99 ,-- schedule
103 ,-- non-Contracted hours
100 )-- UCH
and ScheduleTypeID = 2
And (
@radid is null
OR @radid = sch.RadID
)
) ,
SingleShiftStuff
as (
select
RadID
, max(lu.ShiftTypeName) as ShiftTypeID
, min(sch.StartDateTimeUTC) as FirstStartUTC
, max(sch.EndDateTimeUTC) as LastEndUTC
, max(sch.Remarks) as comments
, SUM(datediff(MINUTE , sch.StartDateTimeUTC ,
sch.EndDateTimeUTC)) as ShiftMin
from
Schedule_cte sch
inner join Schedule.LuShiftType lu
on sch.ShiftTypeID = lu.ShiftTypeID
group by
RadID
) ,
schedule_counts
as (
select
RadID
, COUNT(ScheduleID) as ScheduledTimes
from
Schedule_cte
group by
RadID
) ,
ScheduledTransactions_cte
as (
select
t.TransID
, t.DoctorID as RadID
, t.DatePicked
, t.DateDistributed
, 1 as Scheduled
from
transaction_cte t
cross Apply (
select top ( 1 )
1 as sch
from
Schedule_cte sch
where
t.DoctorID = sch.RadID
and (
(
t.DatePickedUTC <= sch.EndDateTimeUTC
and t.DatePickedUTC >= sch.StartDateTimeUTC
)
OR (
t.DateDistributedUTC <= sch.EndDateTimeUTC
and t.DateDistributedUTC >= sch.StartDateTimeUTC
)
)
) CA1
) ,
startFringe_cte
as (
select
t.TransID
, t.DoctorID as RadID
, t.DatePicked
, t.DateDistributed
, 1 as StartFringe
from
transaction_cte t
cross Apply (
select top ( 1 )
1 as sch
from
Schedule_cte sch
where
t.DoctorID = sch.RadID
and (
(t.DatePickedUTC < sch.StartDateTimeUTC
AND (
t.DateDistributedUTC <= sch.EndDateTimeUTC
and t.DateDistributedUTC >= sch.StartDateTimeUTC
))
)
) CA1
) ,
EndFringe_cte
as (
select
t.TransID
, t.DoctorID as RadID
, t.DatePicked
, t.DateDistributed
, 1 as EndFringe
from
transaction_cte t
cross Apply (
select top ( 1 )
1 as sch
from
Schedule_cte sch
where
t.DoctorID = sch.RadID
and (
(t.DateDistributedUTC > sch.EndDateTimeUTC
AND (
t.DatePickedUTC <= sch.EndDateTimeUTC
and t.DatePickedUTC >= sch.StartDateTimeUTC
))
)
) CA1
) ,
fringe_count
as (
select
fringe.RadID
, SUM(fringe.StartFringe) StartFringeCount
, SUM(fringe.EndFringe) EndFringeCount
from
(
select
fs.TransID
, fs.RadID
, fs.StartFringe
, 0 as EndFringe
from
startFringe_cte fs
union ALL
select
fe.TransID
, fe.RadID
, 0 as StartFringe
, EndFringe
from
EndFringe_cte fe
) as fringe
group by
RadID
) ,
SingleShiftTransactionStuff
as (
select
RadID
, MIN(ScheduledTransactions_cte.DatePicked) as FirstOpened
, MAX(ScheduledTransactions_cte.DateDistributed) as LastSigned
from
ScheduledTransactions_cte
group by
RadID
) ,
UnScheduledTransactions_cte
as (
select
t.TransID
, t.DoctorID as RadID
, 1 as UnScheduled
from
transaction_cte t
where
t.TransID not in ( select
TransID
from
ScheduledTransactions_cte )
) ,
EmptyTime_cte
as (
select
rut.RadID
, sum(ISNULL(DATEDIFF(MINUTE , rut.BeginDateUTC , rut.EndDateUTC) ,
0)) as minEmpty
from
dbo.RadUtilTracking rut
inner join Schedule_cte sch
on rut.RadID = sch.RadID
where
rut.BeginDateUTC >= sch.StartDateTimeUTC
and rut.EndDateUTC <= sch.EndDateTimeUTC
and rut.RadUtilType = 'EMPTY '
group by
rut.RadID
) ,
IdleTime_cte
as (
select
rut.RadID
, sum(ISNULL(DATEDIFF(MINUTE , rut.BeginDateUTC , rut.EndDateUTC) ,
0)) as minIdle
from
dbo.RadUtilTracking rut
inner join Schedule_cte sch
on rut.RadID = sch.RadID
where
rut.BeginDateUTC >= sch.StartDateTimeUTC
and rut.EndDateUTC <= sch.EndDateTimeUTC
and rut.RadUtilType = 'IDLE '
group by
rut.RadID
) ,
TransactionCounts
as (
select
radid
, count(transactions.TransID) as CasesRead
, SUM(Scheduled) as ScheduledReads
, SUM(UnScheduled) as UnScheduledReads
from
(
select
sch.TransID
, RadID
, Scheduled
, 0 as Unscheduled
from
ScheduledTransactions_cte sch
union ALL
select
UnSch.TransID
, RadID
, 0 as Scheduled
, UnScheduled
from
UnScheduledTransactions_cte UnSch
) as transactions
group by
RadID
)
select
rad.RadID
, rad.LastCommaFirst
, ISNULL(schedule_counts.ScheduledTimes , 0) as ScheduledTimes
, CasesRead
, ScheduledReads
, UnScheduledReads
, isnull(minEmpty , 0) as 'Empty(Min)'
, ISNULL(minIdle , 0) as 'Idle(Min)'
, ( case when ISNULL(schedule_counts.ScheduledTimes , 0) = 0 then 'N/A'
when 1 = 1
then cast(ISNULL(minIdle , 0)
/ ISNULL(schedule_counts.ScheduledTimes , 0) as varchar(10))
end ) as 'AvgIdlePerShift(Min)'
, isnull(ShiftMin , 0) as 'Scheduled(Min)'
, isnull(ShiftMin , 0) - isnull(minEmpty , 0) - ISNULL(minIdle , 0) as 'CalculatedReading(Min)'
, isnull(cast(sst.FirstOpened AS varchar(50)) , 'N/A') as FirstOpen
, case when schedule_counts.ScheduledTimes = 1
then cast((
select
DateTimect
from
udf_ConvertUTCTocT(sss.FirstStartUTC)
) AS varchar(50))
when 1 = 1 then 'N/A'
end as ShiftStart
, case when schedule_counts.ScheduledTimes = 1
then cast((
select
DateTimect
from
udf_ConvertUTCTocT(sss.LastEndUTC)
) AS varchar(50))
when 1 = 1 then 'N/A'
end as ShiftEnd
, isnull(cast(sst.LastSigned AS varchar(50)) , 'N/A') as LastSigned
, case when schedule_counts.ScheduledTimes = 1
then isnull(sss.comments , '')
when 1 = 1 then 'N/A'
end as comments
, case when schedule_counts.ScheduledTimes = 1
then isnull(sss.ShiftTypeID , '')
when 1 = 1 then 'N/A'
end as shiftType
, ISNULL(fringe_count.StartFringeCount , 0) as StartFringeCount
, ISNULL(fringe_count.EndFringeCount , 0) as EndFringeCount
from
TransactionCounts
INNER JOIN Rad.Rad as rad
on rad.RadID = TransactionCounts.RadID
LEFT OUTER JOIN schedule_counts
on rad.RadID = schedule_counts.RadID
LEFT OUTER JOIN EmptyTime_cte
on rad.RadID = EmptyTime_cte.RadID
LEFT OUTER JOIN IdleTime_cte
on rad.RadID = IdleTime_cte.RadID
LEFT OUTER JOIN SingleShiftTransactionStuff sst
on rad.RadID = sst.RadID
LEFT OUTER JOIN SingleShiftStuff sss
ON rad.RadID = sss.RadID
LEFT OUTER JOIN fringe_count
ON rad.RadID = fringe_count.RadID
order by
LastCommaFirst
July 29, 2011 at 2:06 pm
Does anyone know why SQL only generates parallel plan with Adhoc but NOT with stored procedure?
July 29, 2011 at 2:12 pm
Attopeu (7/29/2011)
Does anyone know why SQL only generates parallel plan with Adhoc but NOT with stored procedure?
Stored procedures can have parallel plans. Chances are the plans were compiled with different parameters, parameter sniffing, as Ninja mentioned and that's why they're different.
July 29, 2011 at 3:10 pm
I can confirm there is no parameter sniffings because I do not change paramters when calling the proc but I call proc with same parameters multiple times. Also, i can confirm sql is using exing plan (no recompile) and I can verify from below query:
select top 1000 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.cacheobjtype = 'Compiled Plan'
and cp.objtype = 'Proc'
and st.text like '%RadScheduleVariance%'
order by cp.usecounts desc
go
July 30, 2011 at 9:09 am
Please post the execution plans.
One big difference is that, for the adhoc query, the optimizer knows absolutely that the value of @StartTime = '07/08/2011 19:00PM'. The stored procedure has to be ready to serve up results for any value of @StartTime. Try retrieving the value of @startTime from a table in the ad hoc query and see how it performs.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
July 30, 2011 at 11:22 am
Attopeu (7/29/2011)
I can confirm there is no parameter sniffings because I do not change paramters when calling the proc but I call proc with same parameters multiple times. Also, i can confirm sql is using exing plan (no recompile) and I can verify from below query:select top 1000 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.cacheobjtype = 'Compiled Plan'
and cp.objtype = 'Proc'
and st.text like '%RadScheduleVariance%'
order by cp.usecounts desc
go
Wanna bet 100$ on that one?
Post BOTH actual execution plan.
August 1, 2011 at 3:30 am
Ninja's_RGR'us (7/30/2011)
Attopeu (7/29/2011)
I can confirm there is no parameter sniffings because I do not change paramters when calling the proc but I call proc with same parameters multiple times. Also, i can confirm sql is using exing plan (no recompile) and I can verify from below query:select top 1000 st.text, cp.cacheobjtype, cp.objtype, cp.refcounts, cp.usecounts, cp.size_in_bytes, cp.bucketid, cp.plan_handle
from sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_sql_text(cp.plan_handle) st
where cp.cacheobjtype = 'Compiled Plan'
and cp.objtype = 'Proc'
and st.text like '%RadScheduleVariance%'
order by cp.usecounts desc
go
Wanna bet 100$ on that one?
Post BOTH actual execution plan.
I can bet more on it:-D
It's classical params sniffing case.
You have few options:
1. add WITH RECOMPILE option when creating your stored proc. Drawback: in this case your proc will be recompiled every time it's executed
2. Disable parameter sniffing by declaring local variables for each of the input parameter and use them instead of parameters. Drawback: in this case your proc may not use the best possible execution plan by default - you need to check.
3. Create multiple stored procs for different date ranges: one for short and one for wide. The first one (for short range) would potentially use index seeks and the second (for long ranges) would use scans. You can create the wrapper proc which will check the date range and will call appropriate proc. Drawback: more code to maintain
In your place I would try the option 2 first, and if it's not good, use option 3.
However, option 1 may work for you just as well.
Try all, see and choose.
And the most important thing: Do not bet with Ninja on it, you will loose greatly 😀
August 1, 2011 at 5:21 am
I was polite, my first bet was 1K +.
I felt I had a better shot to get a yes at 100$ :w00t:.
Still waiting to see the actual execution.
I like recompile for reports but for oltp it's a lesser choice in my not so humble opinion!
August 1, 2011 at 5:34 am
The query looks like the reporting one...
August 1, 2011 at 5:37 am
Eugene Elutin (8/1/2011)
The query looks like the reporting one...
50%-50% on this particular query. But I was talking more general cases (for future googlers).
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply