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.

Are you saying that the exact same SQL that takes over a minute to run from C# is much quicker from SSMS running on the same machine? — Gabe
– Gabe, Commented Mar 30, 2010 at 21:07
@gabe: yeah, same machine. @Sam: I'm sorry if I am confused. I'm not knowledgeable about this topic nor am I claiming to be. I put down Linq-To-Sql because the library CompiledQuery is located in is System.Data.Linq and I am using it to compile SQL. Is this too much of a stretch? — Brandi
– Brandi, Commented Mar 30, 2010 at 21:16
Linq2Sql is a different way to access your data. It will not solve the problem of things like lacking indexes. It might be interesting to look at though, just to know what it can and cannot do. :-) — Sander Rijken
– Sander Rijken, Commented Mar 30, 2010 at 21:23

Mark Byers · Accepted Answer · 2010-03-30 21:13:20Z

3

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.

edited Mar 30, 2010 at 21:13

answered Mar 30, 2010 at 21:07

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

KM. · Accepted Answer · 2010-03-30 21:10:47Z

2

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.

answered Mar 30, 2010 at 21:10

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

Sander Rijken · Accepted Answer · 2010-03-30 21:09:36Z

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;

answered Mar 30, 2010 at 21:09

Sander Rijken

21.7k3 gold badges65 silver badges86 bronze badges

Collectives™ on Stack Overflow

Compiled Linq Queries with Built-in SQL functions

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related