LINQ to Entities generated SQL inefficient compared to LINQ to SQL generated SQL?

We started to migrate a project from VS2008 with stored procedures to VS2010 with the Entity Framework 4 as ORM.

Now I had to do a rather complex LINQ query to get the data out of our database. I usually first try to figure out the LINQ query using LINQPad (great tool btw!).

Now what I have noticed is that the LINQ to Entities generated SQL is horrible compared to LINQ to SQL, which produced a SQL statement similar like we did before with a stored procedure. The L2E query had so many nested SELECT's and INNER JOINS that it hurt my head by just looking at it!

This got me curious and I picked another rather complex LINQ query and compared the LINQ to Entites generated SQL to LINQ to SQL generated SQL. The result was similar, the L2E SQL was horrible and most likely highly inefficient (I didn't benchmark it, but to me it is clear by looking at the SQL statement)

I'm sorry that I can't give some example queries at the moment, but if the interest is high enough I can set up a dummy database and create queries similar to the ones we use (obfuscating our queries and tables would make the queries unreadable and pointless)

So what is your experience with the Entity Framework? Did nobody notice the awful SQL statements or do most of the people just not care? Am I missing something fundamental about L2E that I should know?

Right now I highly regret that we did choose the Entity Framework over LINQ to SQL.

Edit:

So I did some benchmarks and WOW, I'm surprised! I put the generated SQL statements into a query window in the SQL Server Management Studio and executed each query several times. Here are the average results executing the query in WHILE loop 1, 10 and 100 times.

1 time:
  LINQ to SQL: 440ms
  LINQ to Entities: 240ms

10 times:
  LINQ to SQL: 2900ms
  LINQ to Entities: 910ms

100 times:
  LINQ to SQL: 31600ms
  LINQ to Entities: 7000ms

I'm really surprised!

My next test was to create a simple C# program to generate and execute the SQL statement. I used LINQpad for convenience. For each test I used the LINQ to SQL and the LINQ to Entities data provider in LINQPad. The query is exactly the same each time. The test program looks like this:

void Main()
{
    var sw = new Stopwatch();
    
    for(int i = 0; i < 5; i++)
    {
        sw.Start();
        for(int y = 0; y < 10; y++)
        {
            ExecuteQuery();
        }
        sw.Stop();
        Console.WriteLine(string.Format("Pass {0}: {1}", i, sw.ElapsedMilliseconds));
        sw.Reset();
    }
}
private void ExecuteQuery()
{
    //here is my 'complex' linq query
    var dummy = (from p in....).ToList();
}

This time I'm a bit disappointed, the results are as follows:

LINQ to SQL:
Run 0: 805
Run 1: 726
Run 2: 722
Run 3: 717
Run 4: 767

LINQ to Entities:
Run 0: 3031
Run 1: 3231
Run 2: 3085
Run 3: 3127
Run 4: 3148

I suppose the difference is due to the SQL statement generation? Or am I benchmarking totally wrong? Suggestions?

Edit2: I have implemented the LINQ query in our project (forgot to mention that the project is an ASP.NET MVC 3 RC Web app). We are basically displaying a page with a custom made grid. Loading the data for the grid with LINQ to Entities takes about 300-400ms! While loading the data with LINQ to SQL takes around 70-80ms. This is actually very similar to my results in my test app in LINQPad above.

I have been searching for performance issues with EF for the past few hours and I found a lot of issues with initial queries. And indeed the initial query is slower, but only about 200ms, so the first query takes around 600ms while every other subsequent query takes 300-400ms. I also read about precompiling views with EdmGen but it didn't make a difference. I found another blog post that compared LINQ to SQL performance with EF and the result seems to be similar, although this was for EF 3.5. Then I found a blog post about EF with heavy inheritance, but this doesn't apply to my case.

Other than the hundred of "initial query is slow" type of posts I didn't find anything related to my problem. I mean, I don't mind a performance hit compared to Linq to SQL, but 70ms versus 300ms is a huge difference! It does affect the user experience of our web app.

The last thing I'm going to try is to precompile the query. If the performance doe开发者_如何学运维sn't get better then I'm definitively switching to LINQ to SQL (altough the generated SQL itself is indeed faster!)

You lost me when you said that you didn't actually bother to benchmark the performance.

Just because SQL looks bad, that doesn't mean that it performs terribly (all the time).

When using Entity Framework or LINQ to SQL, I really don't care what the generated statements look like as long as my application still performs to my standards. Only once performance degrades past an acceptable level will I bother fine-tuning the queries to get better SQL.