Databases
StackOverflow2013
Postsdbo
POEntity Framework is slow because of derived tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POEntity Framework is slow because of derived tables
    primarykey
    data
    text
    <p>I am using MySQL Connector/Net 6.5.4 with LINQ to entities, and I frequently get terrible query performance because the entity framework generates queries that use derived tables.</p> <p>Here is a simplified example of what I've encountered several times. In C#, I write a query like this:</p> <pre><code>var culverCustomers = from cs in db.CustomerSummaries where cs.Street == "Culver" select cs; // later... var sortedCustomers = culverCustomers.OrderBy(cs =&gt; cs.Name).ToList(); </code></pre> <p>Instead of generating simple a query like this:</p> <pre><code>SELECT cust.id FROM customer_summary cust WHERE cust.street = "Culver" ORDER BY cust.name </code></pre> <p>The entity framework generates a query with a derived table like this:</p> <pre><code>SELECT Project1.id FROM ( SELECT cust.id, cust.name, cust.street FROM customer_summary cust WHERE Project1.street = "Culver" ) AS Project1 -- here is where the EF generates a pointless derived table ORDER BY Project1.name </code></pre> <p>If I explain both queries I get this for the first query:</p> <pre><code>id, select_type, table, type, possible_keys, rows 1, PRIMARY, addr, ALL, PRIMARY, 9 1, PRIMARY, cust, ref, PRIMARY, 4 </code></pre> <p>... and something awful like this for the entity framework query</p> <pre><code>id, select_type, table, type, possible_keys, rows 1, PRIMARY, &lt;derived2&gt;, ALL, 9639 2, DERIVED, addr, ALL, PRIMARY, 9 2, DERIVED, cust, ref, PRIMARY, 4 </code></pre> <p>Note the first row, where MySQL explains that it's scanning <strong>9000+ records</strong>. Because of the derived table, MySQL is creating a temp table and loading every row. (Or so I'm deducing based on articles like this one: <a href="http://www.mysqlperformanceblog.com/2006/08/31/derived-tables-and-views-performance/" rel="nofollow">Derived Tables and Views Performance</a>)</p> <p><strong>How can I prevent the Entity Framework from using a derived table, or how can I convince MySQL to do the obvious optimization for queries like this?</strong></p> <p>For completion, here is the view that is the source for this linq query:</p> <pre><code>create view customer_summary as select cust.id, cust.name, addr.street customers cust join addresses addr on addr.customer_id = cust.id </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USThisIsTheDave
    1. USThisIsTheDave
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. POEntity Framework is slow because of derived tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POEntity Framework is slow because of derived tables
      1. USjmoerdyk
      1. VTFavorite
    3. VO
      singulars
      1. POEntity Framework is slow because of derived tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. COFYI, [this should see improvement with MySQL 5.6](https://dev.mysql.com/doc/refman/5.6/en/from-clause-subquery-optimization.html), but it will still not be nearly as good as in some other database servers.
      singulars
      1. POEntity Framework is slow because of derived tables
      1. UShvd
    2. COThanks! I will try it out and see if it improves. What DB do you recommend? I have some flexibility.
      singulars
      1. POEntity Framework is slow because of derived tables
      1. USThisIsTheDave
    3. COI can only say this is something MS SQL Server gets right (EF-on-MSSQL uses the same sort of generated SQL, but it works well on that), and that'll for probably obvious reasons be the best supported database server if you're using Entity Framework. There can very well be other reasons why it would be a poor choice for you, though, so don't take this as a hard recommendation.
      singulars
      1. POEntity Framework is slow because of derived tables
      1. UShvd
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload