Compiled Linq Queries with Built-in SQL functions

Question

I have a query that I am executing in C# that is taking way too much time:

string Query = "SELECT COUNT(HISTORYID) FROM HISTORY WHERE YEAR(CREATEDATE) = YEAR(GETDATE()) ";
Query += "AND MONTH(CREATEDATE) = MONTH(GETDATE()) AND DAY(CREATEDATE) = DAY(GETDATE()) AND USERID = '" + EmployeeID + "' ";
Query += "AND TYPE = '5'";

I then use SqlCommand Command = new SqlCommand(Query, Connection) and SqlDataReader Reader = Command.ExecuteReader() to read in the data. This is taking over a minute to execute from C#, but is much quicker in SSMS.

I see from google searching you can do something with CompiledQuery, but I'm confused whether I can still use the built in SQL functions YEAR, MONTH, DAY, and GETDATE.

If anyone can show me an example of how to create and call a compiled query using the built in functions, I will be very grateful! Thanks in advance.

Answer 1

Compiled queries won't help you here unfortunately: the compile time is not likely to be a significant portion of the one minute. I assume you already have an index including some or all of (createdate, userid, type). If not, add an index.

A possible problem is that your use of functions will prevent any index on createdate being used, causing a slow index scan or table scan. Rewrite your query to use BETWEEN instead and it should be able to use the index. That will make it much faster.

Also, use EXPLAIN on your query to see which indexes it is using, if any. Post that information here.

Answer 2

Linq to Sql isn't a magical instrument to solve all your performance issues with queries. In fact it'll generate similar queries. Instead analyze your query to find out where it is slow, and fix those problems by tuning the query and/or adjusting your indexes.

Also I have to note that it's a bad practice to do this:

AND USERID = '" + EmployeeID + "' "

Instead try to use parameterized queries instead. That means that instead you use:

AND USERID=@UserId

And then, before executing the command you set that parameter (assuming NVARCHAR(255), adjust to needs):

commmand.Parameters.Add("@UserId", SqlDbType.NVarChar, 255).Value = EmployeeID;

Answer 3

even if you have an index on CREATEDATE, it will not use it. You need to change your date logic to floor the GETDATE() datetime to a day like this:

WHERE CREATEDATE>=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
   AND CREATEDATE<DATEADD(day,DATEDIFF(day,0,GETDATE()),0)+1 ...

When you use the function on the column, like: YEAR(CREATEDATE), you prevent an index from being used.