So I am trying to create a view that automatically pulls data from the last twelve months (starting from the end of the last month.)
When I run this with a where clause like the following :
WHERE Visit_Date between 'Dec 1 2012' and 'Dec 1 2013'
It will run in a ~1min.
I have some calculations that will automatically create those dates. But when I use them in the where clause the query is still running after 15 minutes.
WHERE Visit_Date between DATEADD(mm,-12,DATEADD(mm,DATEDIFF(mm,12,GETDATE()),0))
and Dateadd(dd,-1,DATEADD(mm,DATEDIFF(mm,12,GETDATE()),0))
The query is running on a table with 50+ million records. I am sure there this is a more efficient way to do this. I am guessing what is happening is its running through the Getdate() calculations for every row, which is obviously not ideal.
Any suggestions? Please keep in mind that I am creating a view, and I don't usually write stored procedures or dynamic SQL.
I think @Andriy is probably right (I've also blogged about it) that this is due to a cardinality estimation bug (the dates are reversed when making estimates). You can see more details in KB #2481274, Connect #630583 and the question @Andriy pointed out:
Query runs slow with date expression, but fast with string literal
For something you only change once a month, I think you could consider creating a job that alters the view at the beginning of every month, hard-coding the date range into the view. This might not be a terrible alternative to enabling trace flag 4199, which may or may not be a permanent fix, and may or may not cause other issues if you turn it on globally (as opposed to just for the session that runs this query - again no guarantee it will always make this fast). Here is the kind of process I am thinking about:
CREATE PROCEDURE dbo.AlterThatView
AS
BEGIN
SET NOCOUNT ON;
DECLARE @end DATE = DATEADD(DAY, 1-DAY(GETDATE()), GETDATE());
DECLARE @start DATE = DATEADD(MONTH, -12, @end);
DECLARE @sql NVARCHAR(MAX) = N'ALTER VIEW dbo.ViewName
AS
SELECT ...
WHERE Visit_Date >= ''' + CONVERT(CHAR(8), @start, 112) + '''
AND Visit_Date < ''' + CONVERT(CHAR(8), @end, 112) + ''';';
EXEC sp_executesql @sql;
END
GO
Just create a job that runs, say, a minute after midnight on every 1st of the month, and calls this procedure. You may want to have the job to run a SELECT
from the view once too.
Please don't use BETWEEN for date range queries and stop using lazy shorthand for dateparts.