StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>When you say that the actual TSQL queries run fast, are you talking about hand-coded queries?</p> <p>Try using SQL Profiler to see what is being generated by EF 3.5. Perhaps this will show why the performance would be so different, and provide some insight into whether and how it would be possible to improve the performance of the first query.</p> <p>Also, here are a couple of blog posts giving specific examples of how sql generation was improved in EF 4. Even if upgrading to EF 4 isn't an option, they might provide food for thought.</p> <p><a href="http://blogs.msdn.com/b/adonet/archive/2009/08/05/improvements-to-the-generated-sql-in-net-4-0-beta1.aspx" rel="nofollow noreferrer">Improvements to the Generated SQL in .NET 4.0 Beta1</a></p> <p><a href="http://blogs.msdn.com/b/adonet/archive/2010/05/10/improvements-to-generated-sql-in-net-4-0.aspx" rel="nofollow noreferrer">Improvements to Generated SQL in .NET 4.0</a></p> <p><strong>Edit</strong></p> <p>Here's the code I used to try and reproduce your results. This is using SQL Server 2008 R2, VS 2010 (no SP1) and Entity Framework 4.0. I had to guess at the schema; hopefully it's close.</p> <p>To create the tables and populate them:</p> <pre><code>set nocount on create table Scopes ( [Id] int identity primary key, [Level] int, [Name] nvarchar(50), [ParentScope_Id] int foreign key references Scopes(Id) ) create table Settings ( [Id] int identity primary key, [Type] nvarchar(20), [Value] nvarchar(50), [Scope_Id] int foreign key references Scopes(Id) ) go declare @scopeId int, @scopeCount int, @settingCount int, @value nvarchar(50) set @scopeCount = 0 while @scopeCount < 10 begin insert into Scopes([Level], [Name]) values(1, 'Scope ' + cast(@scopeCount as nvarchar)) select @scopeId = @@IDENTITY set @settingCount = 0 while @settingCount < 10000 begin set @value = 'Setting ' + cast(@scopeId as nvarchar) + '.' + cast(@settingCount as nvarchar) insert into Settings([Type], [Value], [Scope_Id]) values ('Test', @value, @scopeId) set @settingCount = @settingCount + 1 end set @scopeCount = @scopeCount + 1 end </code></pre> <p>Using a console application to test:</p> <pre><code>using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Diagnostics; namespace so_q5205281 { class Program { static void Main(string[] args) { using (var context = new EFTestEntities()) { int level = 1; string name = "Scope 4"; ExecQuery1(context, level, name); ExecQuery1(context, level, name); ExecQuery1(context, level, name); ExecQuery2(context, level, name); ExecQuery2(context, level, name); ExecQuery2(context, level, name); } Console.ReadLine(); } static void ExecQuery1(EFTestEntities context, int level, string name) { Stopwatch stopwatch = Stopwatch.StartNew(); var scope = context.Scopes.Include("Settings") .Where(s => s.Level == level && s.Name == name) .First(); int settingsCount = scope.Settings.Count(); stopwatch.Stop(); Console.WriteLine("Query 1, scope name: {0}, settings count: {1}, seconds {2}", scope.Name, settingsCount, stopwatch.Elapsed.TotalSeconds); } static void ExecQuery2(EFTestEntities context, int level, string name) { Stopwatch stopwatch = Stopwatch.StartNew(); var scope = context.Scopes .Where(s => s.Level == level && s.Name == name) .First(); var settings = context.Settings.Where(s => s.Scope.Id == scope.Id).ToList(); int settingsCount = scope.Settings.Count(); stopwatch.Stop(); Console.WriteLine("Query 2, scope name: {0}, settings count: {1}, seconds {2}", scope.Name, settingsCount, stopwatch.Elapsed.TotalSeconds); } } } </code></pre> <p>The EF model was created using the default settings, and updating the model from the database:</p> <p><img src="https://i.stack.imgur.com/jftx5.jpg" alt="enter image description here"></p> <p>The sql sent from EF for the first query:</p> <pre><code>exec sp_executesql N'SELECT [Project2].[Id] AS [Id], [Project2].[Level] AS [Level], [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].[Level] AS [Level], [Limit1].[Name] AS [Name], [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].[Level] AS [Level], [Extent1].[Name] AS [Name], [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',N'@p__linq__0 int,@p__linq__1 nvarchar(4000)',@p__linq__0=1,@p__linq__1=N'Scope 4' </code></pre> <p>and for the second query:</p> <pre><code>exec sp_executesql N'SELECT TOP (1) [Extent1].[Id] AS [Id], [Extent1].[Level] AS [Level], [Extent1].[Name] AS [Name], [Extent1].[ParentScope_Id] AS [ParentScope_Id] FROM [dbo].[Scopes] AS [Extent1] WHERE ([Extent1].[Level] = @p__linq__0) AND ([Extent1].[Name] = @p__linq__1)',N'@p__linq__0 int,@p__linq__1 nvarchar(4000)',@p__linq__0=1,@p__linq__1=N'Scope 4' exec sp_executesql N'SELECT [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',N'@p__linq__0 int',@p__linq__0=5 </code></pre> <p>and the output:</p> <pre><code>Query 1, scope name: Scope 4, settings count: 10000, seconds 0.6657546 Query 1, scope name: Scope 4, settings count: 10000, seconds 0.1608498 Query 1, scope name: Scope 4, settings count: 10000, seconds 0.1097625 Query 2, scope name: Scope 4, settings count: 10000, seconds 0.0742593 Query 2, scope name: Scope 4, settings count: 10000, seconds 0.0551458 Query 2, scope name: Scope 4, settings count: 10000, seconds 0.0555465 </code></pre>

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