How does C#'s DateTime.Now affect query plan caching in SQL Server?

Posted by Bill Paetzke on Stack Overflow See other posts from Stack Overflow or by Bill Paetzke
Published on 2010-05-04T22:42:17Z Indexed on 2010/05/04 22:48 UTC
Read the original article Hit count: 271

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.

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2005