Databases
StackOverflow2013
Postsdbo
POVery different performance of EF with very similar queries
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POVery different performance of EF with very similar queries
    primarykey
    data
    text
    <p>We have two Entity Framework queries, one with <code>Include</code> one with standalone query. Here they are</p> <pre><code> ConfigModelContainer model = new ConfigModelContainer(); var scope = model.Scopes.Include("Settings") .Where(s =&gt; (s.Level == intLevel &amp;&amp; s.Name == name)) .First(); ConfigModelContainer model = new ConfigModelContainer(); var scope = model.Scopes .Where(s =&gt; (s.Level == intLevel &amp;&amp; s.Name == name)) .First(); var settings = model.Settings.Where(s =&gt; s.Scope.Id == scope.Id).ToList(); </code></pre> <p>one more case that has same performance as first one (Query2)</p> <pre><code> var scope1 = model.Scopes .Where(s =&gt; (s.Level == intLevel &amp;&amp; s.Name == name)) .First(); scope1.Settings.Load(); </code></pre> <p>First one runs for 30 seconds, second runs for sub-second. This is so weird, that I have no ideas.</p> <p>Does anyone know why this might happen?</p> <p><strong>Edit</strong>: Actual TSQL queries run very fast (subsecond)</p> <p><strong>Edit 2</strong>: Here are queries:</p> <p>First:</p> <pre><code>SELECT [Project2].[Level] AS [Level], [Project2].[Id] AS [Id], [Project2].[Name] AS [Name], [Project2].[ParentScope_Id] AS [ParentScope_Id], [Project2].[C1] AS [C1], [Project2].[Id1] AS [Id1], [Project2].[Type] AS [Type], [Project2].[Value] AS [Value], [Project2].[Scope_Id] AS [Scope_Id] FROM ( SELECT [Limit1].[Id] AS [Id], [Limit1].[Name] AS [Name], [Limit1].[Level] AS [Level], [Limit1].[ParentScope_Id] AS [ParentScope_Id], [Extent2].[Id] AS [Id1], [Extent2].[Type] AS [Type], [Extent2].[Value] AS [Value], [Extent2].[Scope_Id] AS [Scope_Id], CASE WHEN ([Extent2].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1] FROM (SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Level] AS [Level], [Extent1].[ParentScope_Id] AS [ParentScope_Id] FROM [dbo].[Scopes] AS [Extent1] WHERE ([Extent1].[Level] = @p__linq__0) AND ([Extent1].[Name] = @p__linq__1) ) AS [Limit1] LEFT OUTER JOIN [dbo].[Settings] AS [Extent2] ON [Limit1].[Id] = [Extent2].[Scope_Id] ) AS [Project2] ORDER BY [Project2].[Id] ASC, [Project2].[C1] ASC </code></pre> <p>Second:</p> <pre><code>SELECT [Limit1].[Level] AS [Level], [Limit1].[Id] AS [Id], [Limit1].[Name] AS [Name], [Limit1].[ParentScope_Id] AS [ParentScope_Id] FROM ( SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Level] AS [Level], [Extent1].[ParentScope_Id] AS [ParentScope_Id] FROM [dbo].[Scopes] AS [Extent1] WHERE ([Extent1].[Level] = @p__linq__0) AND ([Extent1].[Name] = @p__linq__1) ) AS [Limit1] SELECT 1 AS [C1], [Extent1].[Id] AS [Id], [Extent1].[Type] AS [Type], [Extent1].[Value] AS [Value], [Extent1].[Scope_Id] AS [Scope_Id] FROM [dbo].[Settings] AS [Extent1] WHERE [Extent1].[Scope_Id] = @EntityKeyValue1 </code></pre> <p>Third:</p> <pre><code>SELECT [Limit1].[Level] AS [Level], [Limit1].[Id] AS [Id], [Limit1].[Name] AS [Name], [Limit1].[ParentScope_Id] AS [ParentScope_Id] FROM ( SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[Name] AS [Name], [Extent1].[Level] AS [Level], [Extent1].[ParentScope_Id] AS [ParentScope_Id] FROM [dbo].[Scopes] AS [Extent1] WHERE ([Extent1].[Level] = @p__linq__0) AND ([Extent1].[Name] = @p__linq__1) ) AS [Limit1] SELECT 1 AS [C1], [Extent1].[Id] AS [Id], [Extent1].[Type] AS [Type], [Extent1].[Value] AS [Value], [Extent1].[Scope_Id] AS [Scope_Id] FROM [dbo].[Settings] AS [Extent1] WHERE [Extent1].[Scope_Id] = @p__linq__0 </code></pre> <p><strong>Edit 3</strong>:</p> <p>I was not able to continue tests on same machine. Here are results on faster machine. Here is code and and results:</p> <pre><code> static void Main(string[] args) { int intLevel = 2; string name = "fb226050-4f92-4fca-9442-f76565b33877"; Stopwatch sw = new Stopwatch(); using (CMEntities model = new CMEntities()) { sw.Start(); for (int i = 0; i &lt; 5; i++) { var scope1 = model.Scopes.Include("Settings") .Where(s =&gt; (s.Level == intLevel &amp;&amp; s.Name == name)) .First(); Console.WriteLine("Query:1, Iter:{0}, Time:{1}", i, sw.ElapsedMilliseconds); sw.Reset(); sw.Start(); } } Console.WriteLine(); using (CMEntities model = new CMEntities()) { sw.Start(); for (int i = 0; i &lt; 5; i++) { var scope1 = model.Scopes .Where(s =&gt; (s.Level == intLevel &amp;&amp; s.Name == name)) .First(); scope1.Settings.Load(); Console.WriteLine("Query:2, Iter:{0}, Time:{1}", i, sw.ElapsedMilliseconds); sw.Reset(); sw.Start(); } } Console.WriteLine(); using (CMEntities model = new CMEntities()) { for (int i = 0; i &lt; 5; i++) { var scope = model.Scopes .Where(s =&gt; (s.Level == intLevel &amp;&amp; s.Name == name)) .First(); var settings = model.Settings.Where(s =&gt; s.Scope.Id == scope.Id).ToList(); Console.WriteLine("Query:3, Iter:{0}, Time:{1}", i, sw.ElapsedMilliseconds); sw.Reset(); sw.Start(); } } } } </code></pre> <p>Results:</p> <pre><code>Query:1, Iter:0, Time:2477 Query:1, Iter:1, Time:1831 Query:1, Iter:2, Time:1933 Query:1, Iter:3, Time:1774 Query:1, Iter:4, Time:1949 Query:2, Iter:0, Time:2036 Query:2, Iter:1, Time:1870 Query:2, Iter:2, Time:1921 Query:2, Iter:3, Time:1751 Query:2, Iter:4, Time:1758 Query:3, Iter:0, Time:188 Query:3, Iter:1, Time:201 Query:3, Iter:2, Time:185 Query:3, Iter:3, Time:203 Query:3, Iter:4, Time:217 </code></pre> <p><strong>Edit 4</strong>: I rewrote the code using NHibernate:</p> <pre><code> static void Main(string[] args) { var cfg = new StoreConfiguration(); var sessionFactory = Fluently.Configure() .Database(MsSqlConfiguration.MsSql2005 .ConnectionString("Data Source=.;Initial Catalog=CM;Integrated Security=True;MultipleActiveResultSets=True") ) .Mappings(m =&gt; m.AutoMappings.Add( AutoMap.AssemblyOf&lt;Entities.Scope&gt;(cfg) .Conventions .Add( Table.Is(x =&gt; x.EntityType.Name + "s"), PrimaryKey.Name.Is(x =&gt; "Id"), ForeignKey.EndsWith("_id") ) ) ) .BuildSessionFactory(); Stopwatch sw = new Stopwatch(); for (int i = 0; i &lt; 5; i++) { sw.Start(); var session = sessionFactory.OpenSession(); int intLevel = 2; string name = "fb226050-4f92-4fca-9442-f76565b33877"; var scope = session.CreateCriteria&lt;Entities.Scope&gt;() .SetFetchMode("Settings", FetchMode.Eager) .Add(Restrictions.Eq("Name", name)) .Add(Restrictions.Eq("Level", intLevel)) .UniqueResult&lt;Entities.Scope&gt;(); Console.WriteLine("Query:0, Iter:{0}, Time:{1}", i, sw.ElapsedMilliseconds); sw.Reset(); } } </code></pre> <p>results are:</p> <pre><code>Query:0, Iter:0, Time:446 Query:0, Iter:1, Time:223 Query:0, Iter:2, Time:303 Query:0, Iter:3, Time:275 Query:0, Iter:4, Time:284 </code></pre> <p>So NHibernate forms proper collection 10 times faster than EF. This is really sad.</p> <p>Here is query generated by NHibernate:</p> <pre><code>SELECT this_.id AS id0_1_, this_.name AS name0_1_, this_.LEVEL AS level0_1_, settings2_.scope_id AS scope4_3_, settings2_.id AS id3_, settings2_.id AS id1_0_, settings2_.TYPE AS type1_0_, settings2_.VALUE AS value1_0_, settings2_.scope_id AS scope4_1_0_ FROM scopes this_ LEFT OUTER JOIN settings settings2_ ON this_.id = settings2_.scope_id WHERE this_.name = @p0 AND this_.LEVEL = @p1 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAndrey
    1. USAndrey
    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
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POVery different performance of EF with very similar queries
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POVery different performance of EF with very similar queries
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POVery different performance of EF with very similar queries
      1. This table or related slice is empty.
      1. VTUpMod
    1. COWe're talking EF version 4, right?
      singulars
      1. POVery different performance of EF with very similar queries
      1. USDOK
    2. CO@DOK Application is based on .net 3.5. I am not sure about EF version, which one is shipped with 3.5?
      singulars
      1. POVery different performance of EF with very similar queries
      1. USAndrey
    3. CO@Andrey: If you are using SQL servier run SQL profiler and check both queries. You can rerun them in management studio and check their execution plan.
      singulars
      1. POVery different performance of EF with very similar queries
      1. USLadislav Mrnka
 

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