How does DateTime.Now affect query plan caching in SQL Server?
Question:
Does it pass DateTime.Now
as a parameter to proc preventing the SQL query from being cached in the query plan? If so, then the web app is missing out on huge wins?
Possible Solution:
I thought there DateTime.Today.AddDays(1)
would be a possible solution. It will pass the same date to sql proc (per day). And the user will still get the latest data. Please talk to this as well.
This example:
Let's say we have a stored procedure. It passes data to the user to a web page. User can set date range. If the user sets today's date as the "end date", which includes today's data, the web application passes DateTime.Now
to sql proc.
Let's say that one user runs a report - 5/1/2010
before now
- over and over several times. On the web page, the user sees 5/1/2010
before 5/4/2010
. But the web app passes DateTime.Now
to sql proc as the end date. So the end date in the proc will always be different, even though the user requests a similar date range.
Suppose the number of records in the table and the number of users are large. So any performance counts. Hence the importance of the question.
Example proc and execution (if it helps to understand):
CREATE PROCEDURE GetFooData
@StartDate datetime
@EndDate datetime
AS
SELECT *
FROM Foo
WHERE LogDate >= @StartDate
AND LogDate < @EndDate
Here's an example execution using DateTime.Now:
EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now
Here's an example of execution using DateTime.Today.AddDays (1)
EXEC GetFooData '2010-05-01', '2010-05-05' -- passed in DateTime.Today.AddDays(1)
The same data is returned for both processes since the current time is 2010-05-04 15:41:27
.
a source to share
Since you are calling the stored procedure and not the query directly, then your only query that changes is the actual batch you are sending SQL EXEC GetFooData '2010-05-01', '2010-05-05'
vs. GetFooData '2010-05-01', '2010-05-04 15:41:27'
... This is a trivial batch that will generate a trivial plan. While it is true that from a strict technical point of view, you lose a certain amount of performance, it will be almost immeasurable. The details of why this happens are explained in this answer: Dynamically Generated SQL vs Parameters in SQL Server
The good news is that you will benefit from this minor performance improvement in the SqlClient invocation code. Modify the SqlCommand code to explicitly call the stored procedure:
SqlCommand cmd = new SqlCommand("GetFooData", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@StartDate", dateFrom);
cmd.Parameters.AddWithValue("@EndDate", DateTime.Now);
As a side note, storing the localized times in the database is not a good idea due to the clients being in different time zones than the server and due to the complications of changing daylight. A much better solution is to always store UTC time and just format it to the user's local time in the app.
a source to share
The query plan will be cached regardless of the parameter values. The parameters basically ensure that a sequential, reusable query exists as it is type-safe for SQL Server.
What you want is not a query plan, but a cached result. And it will be affected by the behavior you describe.
Since you seem to only be processing whole days, you can try to skip dates rather than dates to minimize the different parameter values. Also try to cache the query results in your application rather than calling back the database every time.
a source to share
In your case, you're probably fine if the second parameter just drifts upward in real time.
However, it is possible to fall prey to the sniffing parameter , where the first execution (which creates the cache execution plan) is called with parameters that create a plan that is usually not good for other commonly used parameters (or the data profile changes a lot). Later calls may use a plan that is sometimes so bad that it won't even fix properly.
If your data profile changes a lot with different parameters, and the execution plan becomes unsatisfactory with certain parameter options, you can mask the parameters into local variables - this will effectively prevent parameters from escaping in SQL Server 2005. There is also WITH RECOMPILE (either in SP or EXEC, but for heavily named SPs, this is not a viable option.) In SQL Server 2008, I have almost always used OPTIMIZE FOR UNKNOWN, which will avoid creating a plan based on sniffing parameters.
a source to share