How does C#'s DateTime.Now affect query plan caching in SQL Server?
- by Bill Paetzke
Given:
Let's say we have a stored procedure. It reports data back to a user on a webpage. The user can set a date range. If the user sets today's date as the "end date," which includes today's data, the web app passes DateTime.Now to the sql proc.
Let's say that one user runs a report--5/1/2010 to now--over and over several times. On the webpage, the user sees "5/1/2010" to "5/4/2010." But the web app passes DateTime.Now to the sql proc as the end date. So, the end date in the proc will always be different, although the user is querying a similar date range.
Assume the number of records in the table and number of users are large. So any performance gains matter. Hence the importance of the question.
Question:
Does passing DateTime.Now as a parameter to a proc prevent SQL Server from caching the query plan? If so, then is the web app missing out on huge performance gains?
Possible Solution:
I thought DateTime.Today.AddDays(1) would be a possible solution. It would allow the user to get the latest data and always pass the same end date to the sql proc--"5/5/2010" in this case. Please speak to this as well.
Sample proc and execution (if that helps to understand):
CREATE PROCEDURE GetFooData
@StartDate datetime
@EndDate datetime
AS
SELECT *
FROM Foo
WHERE LogDate >= @StartDate
AND LogDate < @EndDate
Here's a sample execution using DateTime.Now:
EXEC GetFooData '2010-05-01', '2010-05-04 15:41:27' -- passed in DateTime.Now
Here's a sample 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 procs, since the current time is: 2010-05-04 15:41:27.
