CODEWITHSUNDEEP
linqlinq-to-entities
SWa
SWa

Reputation: 4363

Increase linq query effieciency

I'm optimizing my web application and have run into a bottle neck where the SQL generated from my linq expression is very slow.

The following SQL executes in well under a second:

SELECT
    ISNULL(COUNT(distinct JOBIDNumber),0),
    ISNULL(SUM(JIMQuantityActual * JIMNetMarginFactor),0),
    ISNULL(sum((isnull(MATRecoverablePercent,0) / 100) * JIMQuantityActual * JIMNetMarginFactor),0),
    ISNULL(sum(CarbonSaving),0)
FROM
    dbo.fn_GetJobsForUser(183486) jb
    inner join cd_JobMaterial on JIMJobId = jb.JOBIDNumber
WHERE
    JOBCollectionDate >= '2014-11-01'

Whereas the sql output by the following query takes between 4 and 16 seconds over the same data:

DateTime d = new DateTime(2014, 11, 1)

from job in sp.sp_GetJobsForUser(183486)
where job.JOBCollectionDate >= d
join material in UnitOfWork.Repository<cd_JobMaterial>().Queryable()
on job.JOBIDNumber equals material.JIMJobId 
group material by 1 into f
select new
{
     Jobs = f.Distinct().Count(),
     Weight = f.Sum(x=> x.JIMQuantityActual * x.JIMNetMarginFactor),
     Carbon = f.Sum(x=> x.CarbonSaving),
     Recovery = f.Sum(x => ((x.MATRecoverablePercent / 100) * x.JIMQuantityActual * x.JIMNetMarginFactor))
}

Which outputs the following:

-- Region Parameters
DECLARE @contactId Int = 183486
DECLARE @p__linq__0 DateTime2 = '2014-11-01 00:00:00.0000000'
-- EndRegion

SELECT
    [Project4].[C1] AS [C1],
    [Project4].[C5] AS [C2],
    [Project4].[C2] AS [C3],
    [Project4].[C3] AS [C4],
    [Project4].[C4] AS [C5]
FROM (SELECT
    [Project2].[C1] AS [C1],
    [Project2].[C2] AS [C2],
    [Project2].[C3] AS [C3],
    [Project2].[C4] AS [C4],
    (SELECT
        COUNT(1) AS [A1]
    FROM (SELECT DISTINCT
        /*Fields omitted for brevity */
    FROM [dbo].[fn_GetJobsForUser](@contactId) AS [Extent3]
    INNER JOIN (SELECT
        /*Fields omitted for brevity */
    FROM [dbo].[cd_JobMaterial] AS [cd_JobMaterial]) AS [Extent4]
        ON [Extent3].[JOBIDNumber] = [Extent4].[JIMJobId]
    WHERE ([Extent3].[JOBCollectionDate] >= @p__linq__0)
    AND ([Project2].[C1] = 1)) AS [Distinct1])
    AS [C5]
FROM (SELECT
    @contactId AS [contactId],
    @p__linq__0 AS [p__linq__0],
    [GroupBy1].[K1] AS [C1],
    [GroupBy1].[A1] AS [C2],
    [GroupBy1].[A2] AS [C3],
    [GroupBy1].[A3] AS [C4]
FROM (SELECT
    [Project1].[K1] AS [K1],
    SUM([Project1].[A1]) AS [A1],
    SUM([Project1].[A2]) AS [A2],
    SUM([Project1].[A3]) AS [A3]
FROM (SELECT
    1 AS [K1],
    [Project1].[JIMQuantityActual] * [Project1].[JIMNetMarginFactor] AS [A1],
    [Project1].[CarbonSaving] AS [A2],
    ([Project1].[MATRecoverablePercent] / CAST(100 AS DECIMAL(18))) * [Project1].[JIMQuantityActual] * [Project1].[JIMNetMarginFactor] AS [A3]
FROM (SELECT
    [Extent2].[MATRecoverablePercent] AS [MATRecoverablePercent],
    [Extent2].[JIMQuantityActual] AS [JIMQuantityActual],
    [Extent2].[JIMNetMarginFactor] AS [JIMNetMarginFactor],
    [Extent2].[CarbonSaving] AS [CarbonSaving]
FROM [dbo].[fn_GetJobsForUser](@contactId) AS [Extent1]
INNER JOIN (SELECT
    /*Fields omitted for brevity */
FROM [dbo].[cd_JobMaterial] AS [cd_JobMaterial]) AS [Extent2]
    ON [Extent1].[JOBIDNumber] = [Extent2].[JIMJobId]
WHERE [Extent1].[JOBCollectionDate] >= @p__linq__0) AS [Project1]) AS [Project1]
GROUP BY [K1]) AS [GroupBy1]) AS [Project2]) AS [Project4]

How do I re-write the linq expression to produce more efficient sql or is it just a case of writing a stored procedure and using that instead?

Upvotes: 2

Views: 52

Answers (1)

Ocelot20
Ocelot20

Reputation: 10800

Unfortunately that's one of the downsides of using something as flexible as Entity Framework that has to support a wide variety of complex translations. It obviously comes with great benefit in other areas though, so you'll have to balance those with the performance aspect.

Even if you could find a way to rewrite the query that would generate better SQL now, that's subject to changes in the underlying provider in future versions. Enjoy the clean, concise code for as long as you can before performance is no longer acceptable for your application. If EF isn't cutting it at that point, then use some of the hooks provided to execute raw SQL, stored procedures, etc. that won't be as pretty.

Upvotes: 1

Related Questions