How to optimize SQL query generated by Entity Framework in SQL Server Management Studio?

Question

I create a query in linq which returns a table of most active salesmen in my shop:

ProjectDB3Context db = new ProjectDB3Context();

db.Database.Log = message => Trace.WriteLine(message);
var result = db.tblUsers.Join(db.tblSales,
                              u => u.ID,
                              sl => sl.tblUserId,
                              (u, sl) => new { u, sl })
               .Select(o => new
                            {
                                UserId = o.u.ID,
                                Login = o.u.UserLogin,
                                FullName = o.u.Name + " " +  o.u.Surname,
                                ItemsToSell = db.tblSales.Where(x => x.tblUserId == o.u.ID).Count()
                            })
               .Distinct()
               .OrderByDescending(x => x.ItemsToSell)
               .ToList();

The henerated SQL query looks like:

SELECT 
    [Distinct1].[C1] AS [C1], 
    [Distinct1].[ID] AS [ID], 
    [Distinct1].[UserLogin] AS [UserLogin], 
    [Distinct1].[C2] AS [C2], 
    [Distinct1].[C3] AS [C3]
    FROM ( SELECT DISTINCT 
        [Project1].[ID] AS [ID], 
        [Project1].[UserLogin] AS [UserLogin], 
        1 AS [C1], 
        [Project1].[Name] + N' ' + [Project1].[Surname] AS [C2], 
        [Project1].[C1] AS [C3]
        FROM ( SELECT 
            [Extent1].[ID] AS [ID], 
            [Extent1].[UserLogin] AS [UserLogin], 
            [Extent1].[Name] AS [Name], 
            [Extent1].[Surname] AS [Surname], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[tblSale] AS [Extent3]
                WHERE [Extent3].[tblUserId] = [Extent1].[ID]) AS [C1]
            FROM  [dbo].[tblUser] AS [Extent1]
            INNER JOIN [dbo].[tblSale] AS [Extent2] ON [Extent1].[ID] = [Extent2].[tblUserId]
        )  AS [Project1]
    )  AS [Distinct1]
    ORDER BY [Distinct1].[C3] DESC

Statistics:

SQL Server Execution Times:
CPU time = 359 ms,  elapsed time = 529 ms.

Execution plan screen shot

I want to optimize the generated SQL query and insert optimized query into a stored procedure. SQL Server Management Studio gives me a tip to create a nonclustered index (tblUserId) on tblSale (you can see this tip in image that I included).

When I create it using command:

CREATE NONCLUSTERED INDEX IX_ProductVendor_tblUserId 
ON tblSale (tblUserId);

and then run the SQL query in SQL Server Management Studio I get:

SQL Server Execution Times:
CPU time = 328 ms,  elapsed time = 631 ms.

So it takes much longer after I used index to optimize my SQL query.

Can anybody help me with optimize this query in SQL Server using indexes?

It doesn't take much longer after the index is created - CPU time is actually down .... have you looked at the actual execution plans for the query before creating the index, and after? See what's changed - it might show you additional tips on how to further improve your query — marc_s
– marc_s, Commented Jan 16, 2016 at 11:41
Something is wrong with your query. Start with why joining the two tables (which multiplicates the records in tblUsers) and then not using the second table record in the projection. — Ivan Stoev
– Ivan Stoev, Commented Jan 16, 2016 at 11:45

Ivan Stoev · Accepted Answer · 2016-01-16 12:19:12Z

3

Can anybody help me with optimize this query in SQL Server using indexes?

First off, before trying to optimize the SQL query in database, make sure your LINQ query is optimal. Which is not the case with yours. There is unnecessary join which in turn requires distinct etc. And tblSales is accessed twice (see the generated SQL).

What you are trying to achieve is to get users with sales ordered by sales count descending. The following simple query should produce the desired result

var result = db.tblUsers
   .Select(u => new
   {
       UserId = u.ID,
       Login = u.UserLogin,
       FullName = u.Name + " " +  u.Surname,
       ItemsToSell = db.tblSales.Count(s => s.tblUserId == u.ID)
    })
    .Where(x => x.ItemsToSel > 0)
    .OrderByDescending(x => x.ItemsToSell)
    .ToList();

Try and see the new execution plan/time.

answered Jan 16, 2016 at 12:19

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

marc_s · Accepted Answer · 2016-01-16 12:42:15Z

I want to optimize the generated SQL query and insert optimized query into a stored procedure.

Bzzt. Wrong.

Your query is already "optimized" - in that there isn't anything you can do to the query itself to improve its runtime performance.

Stored procedures in SQL Server do not have any kind of magic-optimization or other real advantages over immediately-executed queries. Stored procedures do benefit from cached execution plans, but so do immediate-queries after their first execution, and execution-plan generation isn't that expensive an operation.

Anyway, using stored procedures for read-only SELECT operations is inadvisble, it is better to use an UDF (CREATE FUNCTION) so you can take advantage of function composition which can be optimized and runtime far better than nested stored procedure calls.

If SQL Server's Show Execution Plan feature tells you to create an index, that is outside of EF's responsibility, it is also outside a stored procedure's responsibility too. Just define the index in your database and include it in your setup script. Your EF-generated query will run much faster without it being a stored procedure.

Collectives™ on Stack Overflow

How to optimize SQL query generated by Entity Framework in SQL Server Management Studio?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related