"Linq to sql" Query Optimization

Question

I am Working with Linq queries of select at the time of debugging I found some Unusual thing.

This is the Linq query for selecting particular columns from table

          var result = from p in Context.Accounts
                join a in _Context.People on p.PersonId equals a.PersonId
                join b in _Context.BusinessTypes on p.BusinessTypeId equals
                b.BusinessTypeId where b.Name == Operator 
                && p.AccountId == AccId && a.PersonId == Pid && p.IsDelete == false
                select new
                {
                   AccountId = p.AccountId,
                   PersonId = p.PersonId,
                   AccountName = p.AccountName,
                   Active = p.Active,
                };

This is the Linq To SQL Conversion which is of same query

SELECT 
[Filter1].[AccountId] AS [AccountId], 
[Filter1].[PersonId1] AS [PersonId], 
[Filter1].[FirstName] AS [FirstName], 
[Filter1].[LastName] AS [LastName], 
[Filter1].[AccountName] AS [AccountName], 
[Filter1].[Active1] AS [Active]
FROM  (SELECT [Extent1].[AccountId] AS [AccountId], [Extent1].[BusinessTypeId] AS [BusinessTypeId],
 [Extent1].[PersonId] AS [PersonId1], [Extent1].[Active] AS [Active1], [Extent1].[AccountName] AS [AccountName], 
 [Extent2].[PersonId] AS [PersonId2], [Extent2].[FirstName] AS [FirstName], [Extent2].[LastName] AS [LastName]
    FROM  [ysmgr].[Account] AS [Extent1]
    INNER JOIN [ysmgr].[Person] AS [Extent2] ON [Extent1].[PersonId] = [Extent2].[PersonId]
    WHERE 0 = [Extent1].[IsDelete] ) AS [Filter1]
INNER JOIN [ysmgr].[BusinessType] AS [Extent3] ON [Filter1].[BusinessTypeId] = [Extent3].[BusinessTypeId]
WHERE ([Extent3].[Name] = Operator) AND ([Filter1].[AccountId] = AccId ) AND ([Filter1].[PersonId2] = Pid)

We can see that in conversion it in second select query(i.e Extent1) it selects the all columns and after that it selects the particular colums (first select i.e. Filter1) .

Does anyone know why it happens?

Thanks @Mitch, you might be right but can you explain me this it might help to me? — cracker, May 24 '14 at 05:30
@TomTom I am not writting the sql query it's LINQ to SQL Conversion. so it's very helpful to me if you can help me to optimize my LINQ query :) — cracker, May 24 '14 at 05:33
And oh, it is never LINQ. LINQ can not generate SQL. LINQ is an API in the cmopiler to generate query trees. It is a specific provider you talk about. Entity Framework? It also has VERSIONS. — TomTom, May 24 '14 at 05:37
@TomTom, As you said Query is fine but I am afraid that It might affect the Performance of the system as I have more then 100,00,000 rows in table. — cracker, May 24 '14 at 05:53
Worry about it after it becomes a performance problem. And if that ever happens, ensuring your tables are properly indexed for the ways they're being accessed should be the first thing you look at. — hatchet - done with SOverflow, May 24 '14 at 06:12
Yeah Thanks @hatchet for the help now i got how i need to go. — cracker, May 24 '14 at 06:14
Look at the execution plan. Should be the same as for the equivalent query without any derived tables. — Martin Smith, May 24 '14 at 07:40

score 2 · Accepted Answer · edited May 23 '17 at 12:27

I consider choosing a tool as signing an MoU: I do my job, you do your job.

Your job is to write correct application code. Entity Framework's job (or one of it) is to convert LINQ into correct SQL. With each version of EF, SQL generation has improved and this is an ongoing process. This means that any tweaks in your code may prove unnecessary, or even counter-productive, with newer versions of EF. Also, a minute modification in a LINQ query that produces the SQL you like, may produce completely different SQL.

That said, it's always sensible to keep an eye on the generated SQL, as you do. If for some reason EF fails badly in producing efficient SQL, you should know.

And then there are known cases where choosing the right LINQ constructs makes a difference. To list a few:

Forcing inner joins. Navigation properties or Includes can produce outer joins. An explicit join in LINQ will always be an inner join in SQL (and this won't change with upgrades).
Avoiding constructs that are notoriously bad. This depends on the query provider. For instance, with Sql Server, performance deteriorates rapidly when Contains is called with "many" items. Also, using set operators like Except, Intersect, Any, or All can produce horrible, non-scalable SQL queries. But later versions may be better at this.
Avoiding forced execution. Or: defer execution as long as possible. Take these two statements:
```
var name = context.Companies.Single(c => c.CompanyId == id).Name;
var name = context.Companies.Where(c => c.CompanyId == id)
                  .Select(c => c.Name).Single();
```
The first query is shorter, better readable, so let's go for it! Or...? Single is one of the LINQ statements that forces query execution. Here it draws the whole record into memory, and then only Name is actually used. This can be very inefficient with large records. The second query only fetches Name from the database.

As for your query, I wouldn't worry about the generated SQL. It's longer and clunkier than what a human being would produce, but it's not too bad. And, as said in the comments, the database engine's query optimizer will probably be able to turn it into an efficient query plan. You may even consider using navigation properties in stead of explicit joins.

Also, as said, your first and main concern is correctness. Tackle performance when it becomes an issue. So far, with EF against Sql Server I've never found incorrect results from LINQ statements and I think that's a tremendous achievement from the EF team.

"Linq to sql" Query Optimization

1 Answers1