Using a less precise date/time value accross a page load, to improve MS-SQL query performance

 

Instead of using GETDATE() SQL built-in function (that returns the exact current date/time with miliseconds), we're using the date/time value of a global variable, defined at the begining of the request :

 

GlobalDateRenderingSql = "'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm") + "'";

 

By converting to string with the format HH:mm, we remove the seconds, so all requests in the same minute will use the same date/time value.

 

Then the SQL Query will look like :

 

string sql = "fContentList(" + GlobalDateRenderingSql + ");"

 

where fContentList is a Table-valued Function, with a datetime parameter.

 

The original query was :
 

string sql = "fContentList(getDate());"

 

This change will drammaticaly increase speed, and lower cpu-usage, as SQL Server will re-use the same path again and again for similar queries.