C# linq optimization OrderByDescending()

Question

I am using .OrderByDescending(x => x.Id) to return the most recent data in paged sets of 20 from a large dataset to a web application. I have been attempting to optimize the speed of my query and noticed that when I remove the OrderByDescending() call it improves the query time from around 8 or 9 seconds down to 2 seconds. I assume the query with OrderByDescending() is starting at the first entry (the lowest ID Number) and looping through all of the data before returning the last 20 or so results. If this is the case is there anyway to make my query start at the last entry (the highest ID Number) and work it's way to the lowest?

In regards to some of the questions I am using MS SQL Server and the LINQ query below is the one I am attempting to optimize

TransactionDao.GetAll()
    .Where(x => x.Order.Location.Id == locationId && x.Status == Status.Complete)
    .OrderByDescending(x => x.Id)
    .Skip((pageNumber - 1) * pageSize)
    .Take(pageSize).ToList();

What are you using to load the data from? Why not set that to return the data in the order desired? — bechbd, Jun 03 '15 at 01:37
It depends on indexing, you can index id be descending : http://stackoverflow.com/questions/743858/sql-server-indexes-ascending-or-descending-what-difference-does-it-make — Peyman, Jun 03 '15 at 01:40
What database are you using? Can you provide your full LINQ query? How big is the total dataset? Is the 'Id' column indexed? — sstan, Jun 03 '15 at 01:56

score 1 · Answer 1 · answered Jun 03 '15 at 04:19

I am going to assume that the data source is SQL server, however if it is not please update accordingly. If it is SQL server here is the process I use to tune a LINQ statement.

Start SQL Profiler by using SQL Management Studio -> Tools -> Profiler
For simplicity choose the default options
Run the application
Put a breakpoint before the linq statement is run
When the breakpoint is hit switch to profiler and use the eraser to clear all previous queries.
Run the linq statement
Find the actual executed SQL statement
Copy into Management studio
Select Query -> Include actual execution plan
Execute the query and review the execution plan. Review what it is doing and see if there are any index recommendations

Optional Select Query -> Analyse Query in Database Engine Tuning Analyzer which may provide additional index recommendations.

One thing that I have seen a lot of developers do is to perform and .ToList() or to EntityCollection() before a select within a data layer. This defeats the purpose, because as soon as this is done the query is performed on the database and any subsequent filter's or orderby's are performed in memory. It is better to have your data layer return the IQueryable so that filter is performed on the database instead.

Hopefully this gives you enough information to find the answer to your problem

score 0 · Answer 2 · answered Jun 03 '15 at 04:20

0

I think it depends from indexing, you can try to index your id and related columns. Next, you can try to execute by sql query. Or maybe you tolist() before query so the speed is slow.

answered Jun 03 '15 at 04:20

tran.phuong02

1

score 0 · Answer 3 · answered Jun 03 '15 at 04:36

Can you post whole Linq query?

From your question I assume that you don't use LingToSql and just working on the data returned from SQL. The best option is of course to return only last few row into application, if possible.

Basic collections in C# doesn't have an index, so if you want to take last few rows from the list in sorted order you have to sort all the records, otherwise you don't know which are the last.

C# linq optimization OrderByDescending()

3 Answers3