Databases
StackOverflow2013
Postsdbo
POSevere performance problems when performing a simple query using Entity Framework
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSevere performance problems when performing a simple query using Entity Framework
    text
    copied!<p>I've got a fairly generic CRUD webapp, which generates pages dynamically according to the contents of several database tables. I'm using Entity Framework 4.0 to pull this data out of the DB, however I'm running into severe performance problems. I've managed to iterate down into a problem which is contained enough that I can detail below.</p> <p>I have a table containing list of <strong>Page Forms</strong> (~200). Each form has one or more <strong>Fields</strong> (~4000 total), and each field has may have some <strong>Parameters</strong> (~16000 total).</p> <p>I've attached a screenshot of my model below:</p> <p><img src="https://i.stack.imgur.com/jCw1H.png" alt="Entity model"></p> <p>The associated entity objects are as follows:</p> <pre><code>public class Form { public int FormID { get; set; } public string FormName { get; set; } public IList&lt;FormField&gt; FormFields { get; set; } } public class FormField { public int FieldID { get; set; } public string FieldName { get; set; } public int FormID{ get; set; } public IList&lt;FormFieldParameter&gt; FormFieldParameters { get; set; } public Form ParentForm { get; set; } } public class FormFieldParameter { public int FieldParamID{ get; set; } public string Value{ get; set; } public int? FieldID { get; set; } public FormField ParentField { get; set; } } </code></pre> <p>The following code pulls out all data for the Form which has an ID of '1'.</p> <pre><code>EntityConnection myConnection = new EntityConnection("name=myModel"); if(conn.State != ConnectionState.Open) { conn.Open(); } ObjectContext context = new ObjectContext("name=myModel"); context.ContextOptions.LazyLoadingEnabled = false; ObjectQuery&lt;PageForm&gt; myObjectSet = context.CreateObjectSet&lt;PageForm&gt;() .Include("FormField.FormFieldParameter"); //Edit: I missed this part out, sorry. In hindsight, this was exactly what was //causing the issue. IEnumerable&lt;PageForm&gt; myObjectSetEnumerable = myObjectSet.AsEnumerable(); IQueryable&lt;PageForm&gt; myFilteredObjectSet = myObjectSetEnumerable.Where(c =&gt; c.FormID == 1) .AsQueryable(); List&lt;PageForm&gt; myReturnValue = myFilteredObjectSet.toList(); </code></pre> <p>Now, while this does work, it runs really poorly. The query takes over a second to run, the entirety of which is spent in the <code>myFilteredObjectSet.toList()</code> call. I ran a profiler on my database to see what was causing the delay, and found that the following query was being generated:</p> <pre><code>SELECT [Project1].[FormID] AS [FormID], [Project1].[FormName] AS [FormName], [Project1].[C2] AS [C1], [Project1].[FormID1] AS [FormID1], [Project1].[FieldID] AS [FieldID], [Project1].[FieldName] AS [FieldName], [Project1].[C1] AS [C2], [Project1].[FieldParamID] AS [FieldParamID], [Project1].[Value] AS [Value], [Project1].[FieldID1] AS [FieldID1] FROM ( SELECT [Extent1].[FormID] AS [FormID], [Extent1].[FormName] AS [FormName], [Join1].[FieldID] AS [FieldID], [Join1].[FieldName] AS [FieldName], [Join1].[FormID] AS [FormID1], [Join1].[FieldParamID] AS [FieldParamID], [Join1].[Value] AS [Value], [Join1].[FieldID1] AS [FieldID1], CASE WHEN ([Join1].[FieldID] IS NULL) THEN CAST(NULL AS int) WHEN ([Join1].[FieldParamID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], CASE WHEN ([Join1].[FieldID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2] FROM [dbo].[PageForm] AS [Extent1] LEFT OUTER JOIN (SELECT [Extent2].[FieldID] AS [FieldID], [Extent2].[FieldName] AS [FieldName], [Extent2].[FormID] AS [FormID], [Extent3].[FieldParamID] AS [FieldParamID], [Extent3].[Value] AS [Value], [Extent3].[FieldID] AS [FieldID1] FROM [dbo].[FormField] AS [Extent2] LEFT OUTER JOIN [dbo].[FormFieldParameter] AS [Extent3] ON [Extent2].[FieldID] = [Extent3].[FieldID] ) AS [Join1] ON [Extent1].[FormID] = [Join1].[FormID] ) AS [Project1] ORDER BY [Project1].[FormID] ASC, [Project1].[C2] ASC, [Project1].[FieldID] ASC, [Project1].[C1] ASC </code></pre> <p>The duration of this query shown on the sql profiler shows that this query is what is taking so long to run. The interesting thing about the query, is that there is no filtering on it at all - It is returning the entire tree! I can't understand why it is returning everything, as the filter <code>myObjectSet.Where(c =&gt; c.FormID == 1)</code> is pretty explicit. The actual returned object only contains a single entry, which I would expect. </p> <p>I'm having this problem across my entire data access layer, and its performance is appalling. I have no idea why the generated query doesn't contain the filter - and no idea how to tell it to do so. Does anybody know the answer?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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