Linq to SQl query performance

Question

I am using Linq to SQl to query the data. When I write a select query with order by and monitor in SQL Server Profiler it run the select command 2 times

to Select the fields from the table
to order the data.

It takes time when the data is huge. Is there any solution for this.

Updated:

from dc in dataContext.UserTable join 
m in dataContext.MonthLookups on dc.Month equals m.Month into sr 
from x in sr.DefaultIfEmpty() 
order by dc.UserName, dc.FirstName 
select new {dc, sr.MothName};

Updated:

SELECT 
 [Project1].[ UserName] AS [UserName], 
[Project1].[ FirstName] AS [FirstName], 
[Project1].[ MonthName] AS [MonthName], 
[Project1].[year] AS [year]
FROM ( SELECT 
     [Extent1].[UserName] AS [UserName], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[year] AS [year], 
     [Extent2].[MonthName] AS [MonthName]
    FROM  [dbo].[DutySavingFin] AS [Extent1]
    LEFT OUTER JOIN [dbo].[MonthLookup] AS [Extent2] ON [Extent1].[Month] = [Extent2].[Month]
)  AS [Project1]
ORDER BY [Project1].[UserName] ASC, [Project1].[FirstName] ASC

Updated :

Preferred way :

SELECT 
     [Extent1].[UserName] AS [UserName], 
    [Extent1].[FirstName] AS [FirstName], 
    [Extent1].[year] AS [year], 
     [Extent2].[MonthName] AS [MonthName]
    FROM  [dbo].[DutySavingFin] AS [Extent1]
    LEFT OUTER JOIN [dbo].[MonthLookup] AS [Extent2] ON [Extent1].[Month] = [Extent2].[Month]
ORDER BY [Extent1].[UserName] ASC, [Extent1].[FirstName] ASC

from dc in dataContext.UserTable join m in dataContext.MonthLookups on dc.Month equals m.Month into sr from x in sr.DefaultIfEmpty() order by dc.UserName, dc.FirstName select new {dc, sr.MothName}; — sugirthini
– sugirthini, Commented May 8, 2012 at 9:33
please edit your question and use a code block and will be a lot easier to read ;) — Guillaume86
– Guillaume86, Commented May 8, 2012 at 9:38
Are you sure you're not calling this twice somewhere (possibly implementing paging somewhere else?) — Paddy
– Paddy, Commented May 8, 2012 at 9:46
To speedup your query you can setup indexes on the columns you want to sort. it also helps to lower the amount of rows using a where statement (including indexes on those params) and sort the data after. — YvesR
– YvesR, Commented May 8, 2012 at 9:50

KristoferA · Accepted Answer · 2012-05-08 10:04:36Z

1

Your example above shows a single query. The nested query does not impact performance if that is what you mean...

If you are working with a large set of data:
1) Make sure you have indexes on DutySavingFin.Month, MonthLookup.Month, and DutySavingFin.UserName+DutySavingFin.FirstName.
2) Add a where clause to filter down to the records you need, unless you really need all data in the table

answered May 8, 2012 at 10:04

KristoferA

12.4k1 gold badge44 silver badges63 bronze badges

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

2 Comments

sugirthini Over a year ago

There is a performance impact to nested query and normal one select query for huge data.

KristoferA Over a year ago

I have never seen that have an impact. Take a look at the execution plan for the L2S-generated query, and then try the same query flattened down without a subquery. Under normal circumstances, the SQL optimizer will generate the same execution plan.

Collectives™ on Stack Overflow

Linq to SQl query performance

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related