Databases
StackOverflow2013
Postsdbo
POCode First Entity Framework Slow When Properties are Missing
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCode First Entity Framework Slow When Properties are Missing
    primarykey
    data
    text
    <p>I'm using Entity Framework 4.3.1 Code First.</p> <p>I have a pretty simple expression and entity model.</p> <pre><code>using (var PMCtx = new PMContext("PMEntities")) { var results = PMCtx.Fetch&lt;vwSDHOriginalMW&gt;() .Where(x =&gt; x.DT &gt;= StartDate &amp;&amp; x.DT &lt; EndDate) .ToList(); return results; } public class vwSDHOriginalMW : IEntityObject, IPMContext { public int Schedule { get; set; } public DateTime DT { get; set; } public int HE { get; set; } public Decimal OrgMW { get; set; } public Decimal DELIVERMW { get; set; } public string NERCCode { get; set; } public string NERCCodeStatus { get; set; } public int SDHSDHID { get; set; } } </code></pre> <p>This was taking 15 seconds every time, not just the first time. The model is mapped to a view in a Sql Server 2008 database. I output the query that EF was sending, and ran it in SSMS and it took a fraction of a second.</p> <p>Why is this so slow in Entity Framework?</p> <p>IEntityObject appears to be a marker interface so that the original programmer could be sure these were the only that get put into the generic.</p> <p>EDIT 1 Fetch ends up going through some layer wrappers to get to the data layer where it does this:</p> <pre><code>private DbSet&lt;TEntity&gt; FetchSet&lt;TEntity&gt;() where TEntity : class, IEntityObject { Type PassedType = typeof(TEntity); if (!CheckedTypes.Any(x =&gt; x.FullName == PassedType.FullName)) if (!PassedType.GetInterfaces().Any(x =&gt; CtxInterfaces.Contains(x))) throw new ArgumentException("Type passed is not a DbSet type of constructed context."); else CheckedTypes.Add(PassedType); return privateContext.Set&lt;TEntity&gt;(); } </code></pre> <p>Cleaned up example of the query EF is sending</p> <pre><code>SELECT [Schedule], [DT], [HE], [OrgMW], [DELIVERMW], [NERCCode], [NERCCodeStatus], [SDHSDHID], [ScheduleDeliveryHourHistoryID] FROM [vwSDHOriginalMW] WHERE ([DT] &gt;= '2/17/2013') AND ([DT] &lt; '2/21/2013') </code></pre> <p>EDIT 2 The view in the database actually had one more column than my entity model had properties. I added the property to the model.</p> <pre><code>public class vwSDHOriginalMW : IEntityObject, IPMContext { public int Schedule { get; set; } public DateTime DT { get; set; } public int HE { get; set; } public Decimal OrgMW { get; set; } public Decimal DELIVERMW { get; set; } public string NERCCode { get; set; } public string NERCCodeStatus { get; set; } public int SDHSDHID { get; set; } //missing property public int ScheduleDeliveryHourHistoryID { get; set; } } </code></pre> <p>After adding the property yesterday, it sped up tremendously for a while, ran in 4 seconds instead of 15. But today it's slow again, and nothing has changed.</p> <p>UPDATE: I have narrowed it down a little further. There are two methods that I can use that end up using the same FetchSet. The one that I am using returns an IQueryable instead of an IEnumerable. This seems normal, and since I am filtering afterward, most desirable. However the method that returns IQueryable takes 15 seconds while the IEnumerable takes less than a second. (I am calling ToList() on both) FetchAll turns out just to be a wrapper that calls Fetch and returns IEnumerable instead of IQueryable</p> <pre><code>public IQueryable&lt;TEntity&gt; Fetch&lt;TEntity&gt;() where TEntity : class, Common.IEntityObject { return privateContext.Fetch&lt;TEntity&gt;(); } public IEnumerable&lt;TEntity&gt; FetchAll&lt;TEntity&gt;() where TEntity : class, Common.IEntityObject { return privateContext.FetchAll&lt;TEntity&gt;(); } </code></pre> <p>If I change </p> <pre><code>IEnumerable&lt;vwSDHOriginalMW&gt; results = PMCtx.Fetch&lt;vwSDHOriginalMW&gt;() .Where(x =&gt; x.DT &gt;= StartDate &amp;&amp; x.DT &lt; EndDate) .ToList(); </code></pre> <p>to</p> <pre><code>IEnumerable&lt;vwSDHOriginalMW&gt; results = PMCtx.Fetch&lt;vwSDHOriginalMW&gt;() .ToList() .Where(x =&gt; x.DT &gt;= StartDate &amp;&amp; x.DT &lt; EndDate); </code></pre> <p>it is fast. But this isn't acceptable, because it seems like I would want my where clause to be passed to the database. In this case on a dev environment the view is only 180 rows, but it has potential to be millions, so I definitely don't want to return all my results into memory before I filter them.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USDodecapus
    1. USDodecapus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COWhat is the "IEntityObject" for? Have you lazy loading disabled? What is a sample SQL that is being generated? What is you Fetch method like?
      singulars
      1. POCode First Entity Framework Slow When Properties are Missing
      1. USAFD
    2. COUpdated original post. IEntityObject is just an empty marker interface. Other details are in the post.
      singulars
      1. POCode First Entity Framework Slow When Properties are Missing
      1. USDodecapus
    3. COI missed this part. Yes, it looks like lazy loading is disabled. Turning it on didn't have any effect. The view only has 184 rows anyway.
      singulars
      1. POCode First Entity Framework Slow When Properties are Missing
      1. USDodecapus
 

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