LinqToSQL DateTime filters?

Question

I've got a linqtosql query filtering and ordering by a datecolumn that takes 20 seconds to run. When I run the generated sqlquery directly on the DB it returns in 0 seconds.

var myObjs = DB.Table
.Where(obj => obj.DateCreated>=DateTime.Today)
.OrderByDescending(obj => obj.DateCreated);

The table has only 100,000 records and the DateTime column is indexed.

Just another in a long line of linqtosql performance grievances. But this one is SOO bad that I'm sure I must be doing something wrong.

Answer 1

I suspect the difference is that although running the generated query only takes 0 seconds, that's because it's not actually showing you all the results if you're using something like Enterprise Manager. Just fetching (and deserializing) all the data for 100,000 results could well take a significant amount of time, but your manual query is probably only showing you the first 20 hits or something similar.

If you run the same SQL in .NET and use a DataReader to fetch all the data, how long does it take then?

If you run server with profiling turned on, how long does it say the query took to execute from LINQ to SQL?

Answer 2

I can't see anything wrong in your query. It would be great to see the T-SQL generated by Linq. Did you try that?

Answer 3

Thanks guys...

The problem was mine, not linq's. For brevity I shortened the query in the question but there was actually another filter that had been applied to a NON indexed column. Adding the index solved the problem.

What through me for a loop though was that, as Jon Skeet suggested, running the query in Sql Mgmt studio gave a false sense of confidence because the query was paged, and very quickly returned the top 20 rows, leaving me to think linq was to blame. So the index problem only showed up in linq and not in sql mgmt studio.