CODEWITHSUNDEEP
linq-to-sql
Tomas
Tomas

Reputation: 18097

Perform query on server

I need to take last 50 records from user statistic table(table has more than one million records). If I run query below I get very slow response and IIS7 Pool memory usage jumps up to 800mb. I thought that the query will be executed on SQL Server and not in application itself. How to optimize this query?

user.Statistics.OrderByDescending(p => p.DateStamp).Take(50);

Upvotes: 0

Views: 36

Answers (1)

Randy Minder
Randy Minder

Reputation: 48402

This query will be executed on the server. My guess is that the query is slow because you don't have an appropriate index on the DateStamp column.

I would strongly recommend you get a copy of LinqPad (if you don't already have a copy), execute this query in LinqPad, see what T-SQL is being sent to the server (which LinqPad allows you to do), take the T-SQL and look at the query execution plan in SSMS. I would bet a table scan is being done, instead of an index seek.

With the appropriate index in place, this query should execute in < 1-2 seconds at most, even with 10 million rows in the table.

Upvotes: 1

Related Questions