"Order By" in LINQ-to-SQL Causes performance issues
- by panamack
I've set out to write a method in my C# application which can return an ordered subset of names from a table containing about 2000 names starting at the 100th name and returning the next 20 names.
I'm doing this so I can populate a WPF DataGrid in my UI and do some custom paging. I've been using LINQ to SQL but hit a snag with this long executing query so I'm examining the SQL the LINQ query is using (Query B below).
Query A runs well:
SELECT TOP (20)
[t0].[subject_id] AS [Subject_id],
[t0].[session_id] AS [Session_id],
[t0].[name] AS [Name]
FROM [Subjects] AS [t0]
WHERE (NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP (100) [t1].[subject_id]
FROM [Subjects] AS [t1]
WHERE [t1].[session_id] = 1
ORDER BY [t1].[name]
) AS [t2]
WHERE [t0].[subject_id] = [t2].[subject_id]
))) AND ([t0].[session_id] = 1)
Query B takes 40 seconds:
SELECT TOP (20)
[t0].[subject_id] AS [Subject_id],
[t0].[session_id] AS [Session_id],
[t0].[name] AS [Name]
FROM [Subjects] AS [t0]
WHERE (NOT (EXISTS(
SELECT NULL AS [EMPTY]
FROM (
SELECT TOP (100) [t1].[subject_id]
FROM [Subjects] AS [t1]
WHERE [t1].[session_id] = 1
ORDER BY [t1].[name]
) AS [t2]
WHERE [t0].[subject_id] = [t2].[subject_id]
))) AND ([t0].[session_id] = 1)
ORDER BY [t0].[name]
When I add the ORDER BY [t0].[name] to the outer query it slows down the query.
How can I improve the second query?
This was my LINQ stuff Nick
int sessionId = 1;
int start = 100;
int count = 20;
// Query subjects with the shoot's session id
var subjects = cldb.Subjects.Where<Subject>(s => s.Session_id == sessionId);
// Filter as per params
var orderedSubjects = subjects
.OrderBy<Subject, string>(
s => s.Col_zero
);
var filteredSubjects = orderedSubjects
.Skip<Subject>(start)
.Take<Subject>(count);
