CODEWITHSUNDEEP
sql-serverlinq-to-sql
sugirthini
sugirthini

Reputation: 47

Linq to SQl query performance

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

  1. to Select the fields from the table
  2. 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

Upvotes: 0

Views: 163

Answers (1)

KristoferA
KristoferA

Reputation: 12397

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

Upvotes: 1

Related Questions