May 20, 2016 at 5:03 am
Hello
I have the following relatively simple code that's part of a much bigger query:
declare @dateToUse date
declare @startDateToUse date
declare @endDateToUse date
select @dateToUse = '2016-05-20'
select @startDateToUse = datefromparts(year(dateadd(month,-25,getdate())), month(dateadd(month,-25,getdate())),01)
select @endDateToUse = dateadd(day,-1,datefromparts(year(getdate()), month(getdate()), 01));
select distinct FIRST_OF_MONTH
from DW_Presentation.dbo.D_CALENDAR
where CALENDAR_DATE >= @startDateToUse
and CALENDAR_DATE < @endDateToUse
It generates the attached query plan
The plan generates a mismatch between actual and estimated rows
From reading around I should look to avoid this and generally do (unless the execute value shows that it executes a number of times)
Am I missing something or is this correct i.e. there are circumstances where the 2 values do differ
If so, what would cause it
For info, I've updated statistics as stale stats could be a reason
Thanks
- Damian
May 20, 2016 at 5:44 am
Variables in ad hoc sql statements don't behave as you would think. Put this in a sproc and try it to see what happens. Then try both sproc and ad hoc with OPTION (RECOMPILE) with the statement.
Speaking of OPTION (RECOMPILE) you should ALWAYS use that for such start/end date report queries. Do you REALLY want to reuse a query plan that was set up for ONE DAY when the next call has TEN YEARS of date range? What about the reverse of that? This is one of the easiest fixes I get when I do a performance review at a new client. 5-6 orders of magnitude performance improvement is easily achieved when you avoid horrible parameter sniffing such as this!! 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 20, 2016 at 6:23 am
Thanks, makes sense
I now have the following:
create procedure ReturnFirstOfMonthList @dateToUse date, @startDateToUse date,
@endDateToUse date
as
set nocount on;
select distinct FIRST_OF_MONTH
from DW_Presentation.dbo.D_CALENDAR
where CALENDAR_DATE >= @startDateToUse
and CALENDAR_DATE < @endDateToUse
option (recompile)
go;
declare @dateToUse date
declare @startDateToUse date
declare @endDateToUse date
select @dateToUse = '2016-05-20'
select @startDateToUse = datefromparts(year(dateadd(month,-25,getdate())), month(dateadd(month,-25,getdate())),01)
select @endDateToUse = dateadd(day,-1,datefromparts(year(getdate()), month(getdate()), 01));
exec ReturnFirstOfMonthList @dateToUse, @startDateToUse, @endDateToUse
This generates the plan attached
The plan differs (see attached)
Actual and estimate still do not match though
Thanks
- Damian
May 20, 2016 at 8:37 am
I don't think they are off by that much, especially given the DISTINCT. How many total rows in the table? Did you check dbcc showstatistics to see how the numbers looked for the period? Is this discrepancy causing some problem?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply