Query translation/optimization

May 10, 2012 at 12:30 PM

I have a basic query which has a compound index on two columns. Here's how I've written the LINQ code:

 

 

var addr = Context.Addresses
                  .Where(r => r.Lender + r.Loan == lend + loan)
                  .OrderBy(r => r.Loan);


This gets translated into the following SQL (I've removed a bunch of fields for brevity)

 

SELECT 
Project1.Loan, 
Project1.Lender, 
Project1.Department
FROM ( SELECT 
	Extent1.Loan, 
	Extent1.Lender, 
	Extent1.Department
	FROM (SELECT 
      Address.Loan, 
      Address.Lender, 
      Address.Department
      FROM Address Address) Extent1
	WHERE (Extent1.Lender + Extent1.Loan) = (@p__linq__0 + @p__linq__1)
)  Project1
ORDER BY Project1.Loan

The problem with this is that the WHERE clause is being added to the Extent1 cursor, not on the Address table. This means this query isn't optimized and takes > 10 seconds vs. < 1 second. 

I would have expected something closer to this:

SELECT 
        Address.Loan, 
        Address.Lender, 
        Address.Department
   FROM Address Address
  WHERE (Address.Lender + Address.Loan) = (@p__linq__0 + @p__linq__1)
  ORDER BY Address.Loan
Just to be clear, it's not the nested SQL queries that are the problem, it's just that the WHERE clause isn't being applied to the inner query on Address.

 

Coordinator
May 10, 2012 at 12:55 PM

I see what you mean that the index won’t be utilized because the where condition is applied to the Extent1 cursor instead of the Address table.

I’m currently working on optimizing the SQL and expect to have a new build with the optimizations available in a couple weeks. I’ll be sure to test for this scenario.

Thanks for pointing this out.

Coordinator
May 11, 2012 at 9:06 AM
Edited May 11, 2012 at 9:09 AM

I was giving this some more thought and realized that the inner query's table alias doesn't match what I would expect from the SQL generation phase.  The inner query is actually coming from the EDMX.  If you view the EDMX as XML you will find that the Address EntitySet element includes a DefiningQuery element which contains the "SELECT ... FROM Address Address."  This happened due to the fact that a primary key (or candidate key) couldn't be determined.  In this case the Entity would be considered in read only mode.  There are a few ways that you can handle this issue.

  • Modify the EDMX file so that the Entity is not read only.  This requires a bit of knowledge of the XML that makes up the EDMX file.
  • Add a candidate key to the table and re-create the model.
  • Switch over to "Code First" instead of using the EDMX.  This will allow you to easily define the model in code instead of having to learn the EDMX XML.  (I would go this route if you are not using some EDMX specific features.  This blog post can get you started with code first.) 

 

So just to be clear... you don't need to wait for the next build to be able utilize your indexes.  You just need to make sure the Entity is not in read only mode.