Linq 2 SQL query optimizing

Question

I am having a real problem with a linq to sql query, and it is probable because my skilz are a bit lacking, but i would appreciate it if anyone can have a look and see how i can optimize the query as the SQL that is being generated is horrific

What the query is doing, is trying to get a stock overview for the locations joining on the sales details table to find out when the last sale was made and the quantity of stock at that location based on a department id (that being all products in a department) hence the Max() and Sum()

var query1 = (
            from sl in slRepo
            where sl.Product.bsDepartmentId < 90 && sl.Product.SubDepartment.Department.Id == departmentId
            group sl by sl.Location into gbsl
            select new
            {
                ProductId = gbsl.FirstOrDefault().Product.Id,
                LocationId = gbsl.FirstOrDefault().Location.Id,
                Location = gbsl.FirstOrDefault().Location.Name,
                Quantity = gbsl.Sum(x => x.CurrentStock),
                LastInvoice = gbsl.Max(x => x.LastInvoice)
            });

var query2 = (
            from sd in sdRepo
            where sd.Product.SubDepartment.Department.Id == departmentId
            group sd by sd.Location.Id into g
            select new
            {
                LocationId = g.FirstOrDefault().Location.Id,
                LastSale = g.Max(x => x.TransactionDate)
            });

var query3 = (
            from q1 in query1
            join q2 in query2 on q1.LocationId equals q2.LocationId into temp
            from j in temp.DefaultIfEmpty()
            select new XStockOverviewDto
            {
                Location = q1.Location,
                Quantity = q1.Quantity,
                LastInvoice = q1.LastInvoice,
                LastSale = j.LastSale
            });

        return query3;

And then the SQL being generated from this is... which seems to being using the same datasets to query the MAX and the SUM separately when it should be done together making the query far to complicated and it takes a hell of a long time to actually query

SELECT 
1 AS [C1], 
[Project10].[Name] AS [Name], 
[Project10].[C2] AS [C2], 
[Project10].[C3] AS [C3], 
 CAST( [Project16].[C2] AS datetime2) AS [C4]
FROM   (SELECT 
    [Project9].[Name] AS [Name], 
    [Project9].[C1] AS [C1], 
    [Project9].[C2] AS [C2], 
    (SELECT 
        MAX([Filter9].[LastInvoice]) AS [A1]
        FROM ( SELECT [Extent18].[LastInvoice] AS [LastInvoice], [Extent20].[Department_Id] AS [Department_Id], [Extent21].[Id] AS [Id1]
            FROM    [dbo].[XStockLevels] AS [Extent18]
            INNER JOIN [dbo].[XProducts] AS [Extent19] ON [Extent18].[Product_Id] = [Extent19].[Id]
            LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent20] ON [Extent19].[SubDepartment_Id] = [Extent20].[Id]
            LEFT OUTER JOIN [dbo].[XLocations] AS [Extent21] ON [Extent18].[Location_Id] = [Extent21].[Id]
            WHERE [Extent19].[bsDepartmentId] < 90
        )  AS [Filter9]
        WHERE ([Filter9].[Department_Id] = @p__linq__0) AND (([Project9].[Id] = [Filter9].[Id1]) OR (([Project9].[Id] IS NULL) AND ([Filter9].[Id1] IS NULL)))) AS [C3]
    FROM ( SELECT 
        [Project8].[Id] AS [Id], 
        [Project8].[Name] AS [Name], 
        [Project8].[C1] AS [C1], 
        (SELECT 
            SUM([Filter7].[CurrentStock]) AS [A1]
            FROM ( SELECT [Extent14].[CurrentStock] AS [CurrentStock], [Extent16].[Department_Id] AS [Department_Id], [Extent17].[Id] AS [Id2]
                FROM    [dbo].[XStockLevels] AS [Extent14]
                INNER JOIN [dbo].[XProducts] AS [Extent15] ON [Extent14].[Product_Id] = [Extent15].[Id]
                LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent16] ON [Extent15].[SubDepartment_Id] = [Extent16].[Id]
                LEFT OUTER JOIN [dbo].[XLocations] AS [Extent17] ON [Extent14].[Location_Id] = [Extent17].[Id]
                WHERE [Extent15].[bsDepartmentId] < 90
            )  AS [Filter7]
            WHERE ([Filter7].[Department_Id] = @p__linq__0) AND (([Project8].[Id] = [Filter7].[Id2]) OR (([Project8].[Id] IS NULL) AND ([Filter7].[Id2] IS NULL)))) AS [C2]
        FROM ( SELECT 
            [Project7].[Id] AS [Id], 
            [Extent13].[Name] AS [Name], 
            [Project7].[C1] AS [C1]
            FROM   (SELECT 
                [Project5].[Id] AS [Id], 
                [Project5].[C1] AS [C1], 
                (SELECT TOP (1) 
                    [Filter5].[Location_Id] AS [Location_Id]
                    FROM ( SELECT [Extent9].[Location_Id] AS [Location_Id], [Extent11].[Department_Id] AS [Department_Id], [Extent12].[Id] AS [Id3]
                        FROM    [dbo].[XStockLevels] AS [Extent9]
                        INNER JOIN [dbo].[XProducts] AS [Extent10] ON [Extent9].[Product_Id] = [Extent10].[Id]
                        LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent11] ON [Extent10].[SubDepartment_Id] = [Extent11].[Id]
                        LEFT OUTER JOIN [dbo].[XLocations] AS [Extent12] ON [Extent9].[Location_Id] = [Extent12].[Id]
                        WHERE [Extent10].[bsDepartmentId] < 90
                    )  AS [Filter5]
                    WHERE ([Filter5].[Department_Id] = @p__linq__0) AND (([Project5].[Id] = [Filter5].[Id3]) OR (([Project5].[Id] IS NULL) AND ([Filter5].[Id3] IS NULL)))) AS [C2]
                FROM ( SELECT 
                    [Project4].[Id] AS [Id], 
                    [Project4].[C1] AS [C1]
                    FROM ( SELECT 
                        [Project2].[Id] AS [Id], 
                        (SELECT TOP (1) 
                            [Filter3].[Location_Id] AS [Location_Id]
                            FROM ( SELECT [Extent5].[Location_Id] AS [Location_Id], [Extent7].[Department_Id] AS [Department_Id], [Extent8].[Id] AS [Id4]
                                FROM    [dbo].[XStockLevels] AS [Extent5]
                                INNER JOIN [dbo].[XProducts] AS [Extent6] ON [Extent5].[Product_Id] = [Extent6].[Id]
                                LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent7] ON [Extent6].[SubDepartment_Id] = [Extent7].[Id]
                                LEFT OUTER JOIN [dbo].[XLocations] AS [Extent8] ON [Extent5].[Location_Id] = [Extent8].[Id]
                                WHERE [Extent6].[bsDepartmentId] < 90
                            )  AS [Filter3]
                            WHERE ([Filter3].[Department_Id] = @p__linq__0) AND (([Project2].[Id] = [Filter3].[Id4]) OR (([Project2].[Id] IS NULL) AND ([Filter3].[Id4] IS NULL)))) AS [C1]
                        FROM ( SELECT 
                            [Distinct1].[Id] AS [Id]
                            FROM ( SELECT DISTINCT 
                                [Filter1].[Id5] AS [Id], 
                                [Filter1].[bsLocationId] AS [bsLocationId], 
                                [Filter1].[Name] AS [Name], 
                                [Filter1].[Code1] AS [Code1], 
                                [Filter1].[Code2] AS [Code2], 
                                [Filter1].[Code3] AS [Code3], 
                                [Filter1].[Code4] AS [Code4], 
                                [Filter1].[Code5] AS [Code5], 
                                [Filter1].[Code6] AS [Code6], 
                                [Filter1].[Code7] AS [Code7], 
                                [Filter1].[Code8] AS [Code8], 
                                [Filter1].[bsCompanyId] AS [bsCompanyId], 
                                [Filter1].[Group] AS [Group]
                                FROM ( SELECT [Extent3].[Department_Id] AS [Department_Id], [Extent4].[Id] AS [Id5], [Extent4].[bsLocationId] AS [bsLocationId], [Extent4].[Name] AS [Name], [Extent4].[Code1] AS [Code1], [Extent4].[Code2] AS [Code2], [Extent4].[Code3] AS [Code3], [Extent4].[Code4] AS [Code4], [Extent4].[Code5] AS [Code5], [Extent4].[Code6] AS [Code6], [Extent4].[Code7] AS [Code7], [Extent4].[Code8] AS [Code8], [Extent4].[bsCompanyId] AS [bsCompanyId], [Extent4].[Group] AS [Group]
                                    FROM    [dbo].[XStockLevels] AS [Extent1]
                                    INNER JOIN [dbo].[XProducts] AS [Extent2] ON [Extent1].[Product_Id] = [Extent2].[Id]
                                    LEFT OUTER JOIN [dbo].[XSubDepartments] AS [Extent3] ON [Extent2].[SubDepartment_Id] = [Extent3].[Id]
                                    LEFT OUTER JOIN [dbo].[XLocations] AS [Extent4] ON [Extent1].[Location_Id] = [Extent4].[Id]
                                    WHERE [Extent2].[bsDepartmentId] < 90
                                )  AS [Filter1]
                                WHERE [Filter1].[Department_Id] = @p__linq__0
                            )  AS [Distinct1]
                        )  AS [Project2]
                    )  AS [Project4]
                )  AS [Project5] ) AS [Project7]
            LEFT OUTER JOIN [dbo].[XLocations] AS [Extent13] ON [Project7].[C2] = [Extent13].[Id]
        )  AS [Project8]
    )  AS [Project9] ) AS [Project10]
LEFT OUTER JOIN  (SELECT 
    [Project15].[C1] AS [C1], 
    (SELECT 
        MAX([Extent28].[TransactionDate]) AS [A1]
        FROM   [dbo].[XSalesDetails] AS [Extent28]
        LEFT OUTER JOIN [dbo].[XProducts] AS [Extent29] ON [Extent28].[Product_Id] = [Extent29].[Id]
        INNER JOIN [dbo].[XSubDepartments] AS [Extent30] ON [Extent29].[SubDepartment_Id] = [Extent30].[Id]
        WHERE ([Extent30].[Department_Id] = @p__linq__1) AND (([Project15].[Location_Id] = [Extent28].[Location_Id]) OR (([Project15].[Location_Id] IS NULL) AND ([Extent28].[Location_Id] IS NULL)))) AS [C2]
    FROM ( SELECT 
        [Project14].[Location_Id] AS [Location_Id], 
        [Project14].[C1] AS [C1]
        FROM ( SELECT 
            [Project12].[Location_Id] AS [Location_Id], 
            (SELECT TOP (1) 
                [Extent25].[Location_Id] AS [Location_Id]
                FROM   [dbo].[XSalesDetails] AS [Extent25]
                LEFT OUTER JOIN [dbo].[XProducts] AS [Extent26] ON [Extent25].[Product_Id] = [Extent26].[Id]
                INNER JOIN [dbo].[XSubDepartments] AS [Extent27] ON [Extent26].[SubDepartment_Id] = [Extent27].[Id]
                WHERE ([Extent27].[Department_Id] = @p__linq__1) AND (([Project12].[Location_Id] = [Extent25].[Location_Id]) OR (([Project12].[Location_Id] IS NULL) AND ([Extent25].[Location_Id] IS NULL)))) AS [C1]
            FROM ( SELECT 
                @p__linq__1 AS [p__linq__1], 
                [Distinct2].[Location_Id] AS [Location_Id]
                FROM ( SELECT DISTINCT 
                    [Extent22].[Location_Id] AS [Location_Id]
                    FROM   [dbo].[XSalesDetails] AS [Extent22]
                    LEFT OUTER JOIN [dbo].[XProducts] AS [Extent23] ON [Extent22].[Product_Id] = [Extent23].[Id]
                    INNER JOIN [dbo].[XSubDepartments] AS [Extent24] ON [Extent23].[SubDepartment_Id] = [Extent24].[Id]
                    WHERE [Extent24].[Department_Id] = @p__linq__1
                )  AS [Distinct2]
            )  AS [Project12]
        )  AS [Project14]
    )  AS [Project15] ) AS [Project16] ON ([Project10].[C1] = [Project16].[C1]) OR (([Project10].[C1] IS NULL) AND ([Project16].[C1] IS NULL))

The query that i am actually trying to convert to link is this

DECLARE @DeptId INT = 1;

SELECT Location_Id, XLocations.Name, CurrentStock, LastInvoice, LastSale
FROM
(SELECT Location_Id, SUM(CurrentStock) AS CurrentStock, MAX(LastInvoice) AS LastInvoice, MAX(LastSale) AS LastSale
   FROM XStockLevels
LEFT JOIN 
(SELECT XProducts.bsDepartmentId, XProducts.bsSubDepartmentId, XProducts.bsItemId, XProducts.Id, LastSale
   FROM XProducts 
  INNER JOIN XSubDepartments ON XProducts.SubDepartment_Id = XSubDepartments.Id
   LEFT JOIN (SELECT Product_Id, MAX(TransactionDate) AS LastSale FROM XSalesDetails GROUP BY Product_Id) XSD ON XSD.Product_Id = XProducts.Id
  WHERE XProducts.bsDepartmentId<90 AND XSubDepartments.Department_Id = @DeptId) XP
ON XStockLevels.Product_Id = XP.Id
GROUP BY Location_Id) Z 
INNER JOIN XLocations ON Z.Location_Id = XLocations.Id

So i think there is some real optimization that needs to happen, and i really cant seem to figure out what i am doing wrong in my linq query.

Cheers Joe.

Why translate it to linq if you have the SQL already. Just execute the SQL statement against the db context. — Magnus
– Magnus, Commented Dec 16, 2014 at 14:54
Because i wanted a pure linq solution and not have any SQL in there what so ever. — jderonde
– jderonde, Commented Dec 17, 2014 at 15:22

Rafal · Accepted Answer · 2014-12-12 11:40:40Z

1

Yes linq to sql and other similar generators tend to fail when your query gets complicated.

Your options are to executed your query manually bypassing linq or to create a function on database and execute that form linq.

answered Dec 12, 2014 at 11:40

Rafal

12.7k34 silver badges56 bronze badges

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

Comments

Ian Ringrose · Accepted Answer · 2014-12-12 12:15:10Z

1

Often the SQL server query optimiser sorts it out.

First look at the resulting query plan by putting the “generated” SQL into SQL management studio and asking for the query plan. The query plan will take a bit of understanding if you are not used to it, but it is worth leaning that skill.

Then if you still find there is a problem with the generated SQL, check you have the correct indexes etc.

Then if all else fails, you need to create a stored proc. Or do more than one Ling to SQL query against the database and combine the results in C#.

answered Dec 12, 2014 at 12:15

Ian Ringrose

52.2k58 gold badges218 silver badges325 bronze badges

Comments

Gert Arnold · Accepted Answer · 2014-12-16 14:46:18Z

1

First thing to improve is replace all expressions like...

gbsl.FirstOrDefault().Location.Id

by...

gbsl.Key.Id

Because it has the same effect, but FirstOrDefault() is translated into very expensive SQL.

Also, in the first LINQ query you should remove the ProductId and Location properties from the projection, because they're not used anywhere.

I'm pretty sure that if you do these things, you will be much closer to the query you finally achieved and it is better readable.

Slightly off-topic (because it doesn't play a role here, yet, but after minor changes it may) is that LINQ-to-SQL can be very inefficient with grouping. If you do it "wrong" (see below), it may execute a GROUP BY query followed by queries for each group to populate the groups.

This is because in SQL, group by is "destructive": it doesn't return full records from the queried table. If you do

SELECT a, SUM(b)
FROM   tableA
GROUP BY a

You will get a result set consisting of two values, not other values in tableA. However, in LINQ you're often not only interested in aggregates, but you may just want to group full entities by some property, like in this simple example:

from a in As
group a by a.Type into g
select new { Type = g.Key, As = g }

This will first execute a GROUP BY a.Type query and then as many queries as there are types to get the entities for each group. For these kinds of groupings, it's nearly always better to just fetch the data first (one query) and do the grouping in memory.

(This is one of the rare areas where Entity Framework beats LINQ-to-SQL wrt query generation)

answered Dec 16, 2014 at 14:46

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

1 Comment

jderonde Over a year ago

This is much more relevant thanks for the insight into the grouping and makes a lot of sense. I actually hadn't noticed the firstOrDefault() on the Key of the group. It would appear that this linq stuff is going to take nearly as much time as SQL took me to learn :S

jderonde · Accepted Answer · 2014-12-16 13:30:36Z

The previous two answers do not actually answer my question... but thanks for the replies

Here is what I ended up doing.

Get LINQPad

Get Linqer Trial (i think its about £30 for the full edition)

Decompile the SQL query in Linqer, and look at the code (it was really messy and needed a lot of cleaning up but it gave me somewhere to work from) - copy the code to LINQPad and start to tweak the code to look better checking the SQL and compiler.

Now my code looks like:

    var query5 = (
        from sl in (
            (from xsl in slRepo
                where
                    xsl.Product.bsDepartmentId < 90 &&
                    xsl.Product.SubDepartment.Department.Id == 1
                group xsl by new
                {
                    xsl.Location.Id
                }
                into g
                select new
                {
                    g.Key,
                    LocationId = g.Key.Id,
                    CurrentStock = (int?) g.Sum(p => p.CurrentStock),
                    LastInvoice = g.Max(p => p.LastInvoice)
                }))
        join ls in (
            (from xsd in sdRepo
                join xp in prodRepo on xsd.Product.Id equals xp.Id into xpJoin
                from xp in xpJoin.DefaultIfEmpty()
                join xs in subDepRepo on xp.SubDepartment.Id equals xs.Id into xsJoin
                from xs in xsJoin.DefaultIfEmpty()
                where
                    xp.bsDepartmentId < 90 &&
                    xs.Department.Id == 1
                group xsd by new
                {
                    xsd.Location.Id
                }
                into g
                select new
                {
                    g.Key,
                    LastSale = (DateTime?)g.Max(p => p.TransactionDate)
                })) on sl.Key equals ls.Key into lsJoin
        from ls in lsJoin.DefaultIfEmpty()
        join l in locRepo on sl.LocationId equals l.Id
        select new XStockOverviewDto
        {
            Location = l.Name,
            Quantity = sl.CurrentStock,
            LastInvoice = sl.LastInvoice,
            LastSale = ls.LastSale
        });

And don't get me wrong its not anywhere near as readable, but I am sure that this could be decomposed further and cleaning up to be more readable

but the SQL now looks like this and runs quicker than it can register a time

SELECT 
    1 AS [C1], 
    [Extent7].[Name] AS [Name], 
    [GroupBy1].[A1] AS [C2], 
    [GroupBy1].[A2] AS [C3], 
    CASE WHEN ([Project1].[C2] IS NULL) THEN CAST(NULL AS datetime2) ELSE  CAST( [Project1].[C1] AS datetime2) END AS [C4]
    FROM    (SELECT 
        [Filter1].[Location_Id] AS [K1], 
        SUM([Filter1].[CurrentStock]) AS [A1], 
        MAX([Filter1].[LastInvoice]) AS [A2]
        FROM ( SELECT [Extent1].[CurrentStock] AS [CurrentStock], [Extent1].[LastInvoice] AS [LastInvoice], [Extent1].[Location_Id] AS [Location_Id], [Extent3].[Department_Id] AS [Department_Id]
            FROM   [dbo].[XStockLevels] AS [Extent1]
            INNER JOIN [dbo].[XProducts] AS [Extent2] ON [Extent1].[Product_Id] = [Extent2].[Id]
            INNER JOIN [dbo].[XSubDepartments] AS [Extent3] ON [Extent2].[SubDepartment_Id] = [Extent3].[Id]
            WHERE [Extent2].[bsDepartmentId] < 90
        )  AS [Filter1]
        WHERE 1 = [Filter1].[Department_Id]
        GROUP BY [Filter1].[Location_Id] ) AS [GroupBy1]
    LEFT OUTER JOIN  (SELECT 
        [GroupBy2].[K1] AS [Location_Id], 
        [GroupBy2].[A1] AS [C1], 
        1 AS [C2]
        FROM ( SELECT 
            [Filter3].[Location_Id] AS [K1], 
            MAX([Filter3].[TransactionDate]) AS [A1]
            FROM ( SELECT [Extent4].[TransactionDate] AS [TransactionDate], [Extent4].[Location_Id] AS [Location_Id], [Extent6].[Department_Id] AS [Department_Id]
                FROM   [dbo].[XSalesDetails] AS [Extent4]
                INNER JOIN [dbo].[XProducts] AS [Extent5] ON [Extent4].[Product_Id] = [Extent5].[Id]
                INNER JOIN [dbo].[XSubDepartments] AS [Extent6] ON [Extent5].[SubDepartment_Id] = [Extent6].[Id]
                WHERE [Extent5].[bsDepartmentId] < 90
            )  AS [Filter3]
            WHERE 1 = [Filter3].[Department_Id]
            GROUP BY [Filter3].[Location_Id]
        )  AS [GroupBy2] ) AS [Project1] ON ([GroupBy1].[K1] = [Project1].[Location_Id]) OR (([GroupBy1].[K1] IS NULL) AND ([Project1].[Location_Id] IS NULL))
    INNER JOIN [dbo].[XLocations] AS [Extent7] ON [GroupBy1].[K1] = [Extent7].[Id]

Thanks Joe.

Collectives™ on Stack Overflow

Linq 2 SQL query optimizing

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related