LINQ query slow, creates Timeout; generated SQL is fine?

Question

I have a fairly complex LINQ query that is often so slow, it creates a System.Data.SqlClient.SqlException: "The wait operation timed out".

However, when I log the generated SQL (by assigning a TextWriter to the DataContext's Log), and execute it directly on the SQL Server, it completes in about 4 seconds, which is fine.

Where does the discrepancy come from and how to debug it?

Edit: I've also noticed in Sql Server Management Studio's Activity Monitor the Processor Time spiking to 100% when the query is executing from .NET, but only 3% or so when I execute the generated SQL query.

I'm not sure how posting my code will help, but since it was requested, here is the code containing the query:

var Db = MyProject.GetDataContext();
var statusPaymentSuccess = new string[] { "SUCCESS", "REMBOURS", "AFTERPAY" };

var items = Db.Orders.Where(item =>
    (siteid == null || item.SiteId == siteid) &&
    (ls_list.Contains(item.OrderOrderLifeCycles.OrderByDescending(it => it.Id).First().OrderLifeCycleId)) &&
    (item.OrderOrderPaymentStatus.Any(ops => statusPaymentSuccess.Contains(ops.OrderPaymentStatus.Code)) &&
        (CycleID == null || item.OrderOrderLifeCycles.First().OrderLifeCycleId == CycleID) &&
        (LocationID == null || item.SaleLocationId == LocationID) &&
        (string.IsNullOrEmpty(SalesPerson) || item.EmployeeName.ToLower() == SalesPerson.ToLower()))
);

var betweenorders = items.Select(it => new OrderBetween()
{
    FirstPayDate = it.OrderOrderPaymentStatus.FirstOrDefault(ops => statusPaymentSuccess.Contains(ops.OrderPaymentStatus.Code)).DateTime,
    OrderTotal = it.TotalAmount,
    VatTotal = it.OrderItems.Sum(it2 => it2.BTWAmount ?? 0),
    Quantity = it.OrderItems.Count,
    SiteId = it.SiteId
});

return betweenorders.Where(item => item.FirstPayDate >= start && item.FirstPayDate < stop)
    .GroupBy(item => item.FirstPayDate.Value.Year + "-" + item.FirstPayDate.Value.Month).Select(
        item =>
            new SaleTotal()
            {
                Count = item.Sum(sub => sub.Quantity),
                Month = item.FirstOrDefault().FirstPayDate.Value.Year + "-" + item.FirstOrDefault().FirstPayDate.Value.Month.ToString().PadLeft(2, '0'),
                Total = item.Sum(sub => sub.OrderTotal),
                VAT = item.Sum(sub => sub.VatTotal)
            }).OrderBy(item => item.Month).ToArray();

where ls_list is a List<int> containing OrderOrderLifeCycles IDs.

The generated SQL query as pulled from the log:

DECLARE @p0 NVarChar(4000) = 'SUCCESS' 
DECLARE @p1 NVarChar(4000) = 'REMBOURS' 
DECLARE @p2 NVarChar(4000) = 'AFTERPAY' 
DECLARE @p3 Decimal(31,2) = '0' 
DECLARE @p4 NVarChar(4000) = '-' 
DECLARE @p5 DateTime = '2016-06-01' 
DECLARE @p6 DateTime = '2016-09-01' 
DECLARE @p7 Int = '4' 
DECLARE @p8 Int = '5' 
DECLARE @p9 Int = '8' 
DECLARE @p10 NVarChar(4000) = 'SUCCESS' 
DECLARE @p11 NVarChar(4000) = 'REMBOURS' 
DECLARE @p12 NVarChar(4000) = 'AFTERPAY' 
DECLARE @p13 NVarChar(4000) = '-' 
DECLARE @p14 NVarChar(4000) = '-' 
DECLARE @p15 Int = '2' 
DECLARE @p16 NChar(1) = '0' 
SELECT [t64].[value] AS [Month], [t64].[value2] AS [Count], [t64].[value22] AS [Total], [t64].[value3] AS [VAT]
FROM (
    SELECT ((CONVERT(NVarChar,DATEPART(Year, (
        SELECT [t23].[value]
        FROM (
            SELECT TOP (1) [t18].[value]
            FROM (
                SELECT (
                    SELECT [t17].[DateTime]
                    FROM (
                        SELECT TOP (1) [t15].[DateTime]
                        FROM [dbo].[OrderOrderPaymentStatus] AS [t15]
                        INNER JOIN [dbo].[OrderPaymentStatus] AS [t16] ON [t16].[Id] = [t15].[OrderPaymentStatusId]
                        WHERE ([t16].[Code] IN (@p0, @p1, @p2)) AND ([t15].[OrderId] = [t14].[Id])
                        ) AS [t17]
                    ) AS [value], [t14].[Id]
                FROM [dbo].[Order] AS [t14]
                ) AS [t18]
            WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t18].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t18].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t18].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t18].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t18].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t18].[value]))))))) AND ([t18].[value] >= @p5) AND ([t18].[value] < @p6) AND (((
                SELECT [t20].[OrderLifeCycleId]
                FROM (
                    SELECT TOP (1) [t19].[OrderLifeCycleId]
                    FROM [dbo].[OrderOrderLifeCycle] AS [t19]
                    WHERE [t19].[OrderId] = [t18].[Id]
                    ORDER BY [t19].[Id] DESC
                    ) AS [t20]
                )) IN (@p7, @p8, @p9)) AND (EXISTS(
                SELECT NULL AS [EMPTY]
                FROM [dbo].[OrderOrderPaymentStatus] AS [t21]
                INNER JOIN [dbo].[OrderPaymentStatus] AS [t22] ON [t22].[Id] = [t21].[OrderPaymentStatusId]
                WHERE ([t22].[Code] IN (@p10, @p11, @p12)) AND ([t21].[OrderId] = [t18].[Id])
                ))
            ) AS [t23]
        )))) + @p14) + (
        (CASE 
            WHEN (CONVERT(Int,DATALENGTH(CONVERT(NVarChar,DATEPART(Month, (
                SELECT [t63].[value]
                FROM (
                    SELECT TOP (1) [t58].[value]
                    FROM (
                        SELECT (
                            SELECT [t57].[DateTime]
                            FROM (
                                SELECT TOP (1) [t55].[DateTime]
                                FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
                                INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
                                WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
                                ) AS [t57]
                            ) AS [value], [t54].[Id]
                        FROM [dbo].[Order] AS [t54]
                        ) AS [t58]
                    WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
                        SELECT [t60].[OrderLifeCycleId]
                        FROM (
                            SELECT TOP (1) [t59].[OrderLifeCycleId]
                            FROM [dbo].[OrderOrderLifeCycle] AS [t59]
                            WHERE [t59].[OrderId] = [t58].[Id]
                            ORDER BY [t59].[Id] DESC
                            ) AS [t60]
                        )) IN (@p7, @p8, @p9)) AND (EXISTS(
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
                        INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
                        WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
                        ))
                    ) AS [t63]
                )))) / 2)) >= @p15 THEN CONVERT(NVarChar,DATEPART(Month, (
                SELECT [t63].[value]
                FROM (
                    SELECT TOP (1) [t58].[value]
                    FROM (
                        SELECT (
                            SELECT [t57].[DateTime]
                            FROM (
                                SELECT TOP (1) [t55].[DateTime]
                                FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
                                INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
                                WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
                                ) AS [t57]
                            ) AS [value], [t54].[Id]
                        FROM [dbo].[Order] AS [t54]
                        ) AS [t58]
                    WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
                        SELECT [t60].[OrderLifeCycleId]
                        FROM (
                            SELECT TOP (1) [t59].[OrderLifeCycleId]
                            FROM [dbo].[OrderOrderLifeCycle] AS [t59]
                            WHERE [t59].[OrderId] = [t58].[Id]
                            ORDER BY [t59].[Id] DESC
                            ) AS [t60]
                        )) IN (@p7, @p8, @p9)) AND (EXISTS(
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
                        INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
                        WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
                        ))
                    ) AS [t63]
                )))
            ELSE REPLICATE(@p16, @p15 - (CONVERT(Int,DATALENGTH(CONVERT(NVarChar,DATEPART(Month, (
                SELECT [t63].[value]
                FROM (
                    SELECT TOP (1) [t58].[value]
                    FROM (
                        SELECT (
                            SELECT [t57].[DateTime]
                            FROM (
                                SELECT TOP (1) [t55].[DateTime]
                                FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
                                INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
                                WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
                                ) AS [t57]
                            ) AS [value], [t54].[Id]
                        FROM [dbo].[Order] AS [t54]
                        ) AS [t58]
                    WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
                        SELECT [t60].[OrderLifeCycleId]
                        FROM (
                            SELECT TOP (1) [t59].[OrderLifeCycleId]
                            FROM [dbo].[OrderOrderLifeCycle] AS [t59]
                            WHERE [t59].[OrderId] = [t58].[Id]
                            ORDER BY [t59].[Id] DESC
                            ) AS [t60]
                        )) IN (@p7, @p8, @p9)) AND (EXISTS(
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
                        INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
                        WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
                        ))
                    ) AS [t63]
                )))) / 2))) + (CONVERT(NVarChar,DATEPART(Month, (
                SELECT [t63].[value]
                FROM (
                    SELECT TOP (1) [t58].[value]
                    FROM (
                        SELECT (
                            SELECT [t57].[DateTime]
                            FROM (
                                SELECT TOP (1) [t55].[DateTime]
                                FROM [dbo].[OrderOrderPaymentStatus] AS [t55]
                                INNER JOIN [dbo].[OrderPaymentStatus] AS [t56] ON [t56].[Id] = [t55].[OrderPaymentStatusId]
                                WHERE ([t56].[Code] IN (@p0, @p1, @p2)) AND ([t55].[OrderId] = [t54].[Id])
                                ) AS [t57]
                            ) AS [value], [t54].[Id]
                        FROM [dbo].[Order] AS [t54]
                        ) AS [t58]
                    WHERE ((([t13].[value4] IS NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NULL)) OR (([t13].[value4] IS NOT NULL) AND ((((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value])))) IS NOT NULL) AND ([t13].[value4] = (((CONVERT(NVarChar,DATEPART(Year, [t58].[value]))) + @p13) + (CONVERT(NVarChar,DATEPART(Month, [t58].[value]))))))) AND ([t58].[value] >= @p5) AND ([t58].[value] < @p6) AND (((
                        SELECT [t60].[OrderLifeCycleId]
                        FROM (
                            SELECT TOP (1) [t59].[OrderLifeCycleId]
                            FROM [dbo].[OrderOrderLifeCycle] AS [t59]
                            WHERE [t59].[OrderId] = [t58].[Id]
                            ORDER BY [t59].[Id] DESC
                            ) AS [t60]
                        )) IN (@p7, @p8, @p9)) AND (EXISTS(
                        SELECT NULL AS [EMPTY]
                        FROM [dbo].[OrderOrderPaymentStatus] AS [t61]
                        INNER JOIN [dbo].[OrderPaymentStatus] AS [t62] ON [t62].[Id] = [t61].[OrderPaymentStatusId]
                        WHERE ([t62].[Code] IN (@p10, @p11, @p12)) AND ([t61].[OrderId] = [t58].[Id])
                        ))
                    ) AS [t63]
                ))))
         END)) AS [value], [t13].[value] AS [value2], [t13].[value2] AS [value22], [t13].[value3]
    FROM (
        SELECT SUM([t8].[value3]) AS [value], SUM([t8].[TotalAmount]) AS [value2], SUM([t8].[value22]) AS [value3], [t8].[value] AS [value4]
        FROM (
            SELECT ((CONVERT(NVarChar,DATEPART(Year, [t7].[value]))) + @p4) + (CONVERT(NVarChar,DATEPART(Month, [t7].[value]))) AS [value], [t7].[value] AS [value2], [t7].[Id], [t7].[value3], [t7].[TotalAmount], [t7].[value2] AS [value22]
            FROM (
                SELECT (
                    SELECT [t3].[DateTime]
                    FROM (
                        SELECT TOP (1) [t1].[DateTime]
                        FROM [dbo].[OrderOrderPaymentStatus] AS [t1]
                        INNER JOIN [dbo].[OrderPaymentStatus] AS [t2] ON [t2].[Id] = [t1].[OrderPaymentStatusId]
                        WHERE ([t2].[Code] IN (@p0, @p1, @p2)) AND ([t1].[OrderId] = [t0].[Id])
                        ) AS [t3]
                    ) AS [value], [t0].[TotalAmount], (
                    SELECT SUM([t5].[value])
                    FROM (
                        SELECT COALESCE([t4].[BTWAmount],@p3) AS [value], [t4].[OrderId]
                        FROM [dbo].[OrderItem] AS [t4]
                        ) AS [t5]
                    WHERE [t5].[OrderId] = [t0].[Id]
                    ) AS [value2], (
                    SELECT COUNT(*)
                    FROM [dbo].[OrderItem] AS [t6]
                    WHERE [t6].[OrderId] = [t0].[Id]
                    ) AS [value3], [t0].[Id]
                FROM [dbo].[Order] AS [t0]
                ) AS [t7]
            ) AS [t8]
        WHERE ([t8].[value2] >= @p5) AND ([t8].[value2] < @p6) AND (((
            SELECT [t10].[OrderLifeCycleId]
            FROM (
                SELECT TOP (1) [t9].[OrderLifeCycleId]
                FROM [dbo].[OrderOrderLifeCycle] AS [t9]
                WHERE [t9].[OrderId] = [t8].[Id]
                ORDER BY [t9].[Id] DESC
                ) AS [t10]
            )) IN (@p7, @p8, @p9)) AND (EXISTS(
            SELECT NULL AS [EMPTY]
            FROM [dbo].[OrderOrderPaymentStatus] AS [t11]
            INNER JOIN [dbo].[OrderPaymentStatus] AS [t12] ON [t12].[Id] = [t11].[OrderPaymentStatusId]
            WHERE ([t12].[Code] IN (@p10, @p11, @p12)) AND ([t11].[OrderId] = [t8].[Id])
            ))
        GROUP BY [t8].[value]
        ) AS [t13]
    ) AS [t64]
ORDER BY [t64].[value]

How many rows does the query return ? Is the server on the network or a local one ? — Zein Makki, Aug 08 '16 at 11:40
@Preotector - I think it is a good idea to add the query so we can try and understand what might make it slow (and as you said not due to what linq you decided to do but as in how it interacts with the DB) — Gilad Green, Aug 08 '16 at 11:41
try comparing execution plan using SQL Profiler - First, run your linq command and see the execution plan, after that, run your query in SSMS and see the execution plan... there are a few things which differ based on the execution context (ARTHABORT ON/OFF is one of them, but without looking at your query, we can't be sure if your query has to do anything with that)... but comparing execution plans will give you a good idea of where the things are slow — Nirman, Aug 08 '16 at 11:44
It's hard to suggest something without seeing an actual query, however you could try breaking that query into smaller sub-queries and running them against database separately, then check which one is taking the longest to complete. This might help to narrow down the problem's scope. — Ignas, Aug 08 '16 at 11:49
@Nirman: How can I get the execution plan of the LINQ query? Is that a feature of Visual Studio? — Protector one, Aug 08 '16 at 11:49
Using SQL Profiler you can se all queries executed on the SQL server. — Magnus, Aug 08 '16 at 11:54
What you are describing (fast in SSMS, slow in EF) is usually a symptom for parameter sniffing problem. — Ivan Stoev, Aug 08 '16 at 11:58
@Ivan: What's that? How do I find out if it's causing my issues? — Protector one, Aug 08 '16 at 12:05
You might find this helpfult [Why is Entity Framework taking 30 seconds to load records when the generated query only takes 1/2 of a second?](http://stackoverflow.com/questions/686554/why-is-entity-framework-taking-30-seconds-to-load-records-when-the-generated-que/693734#693734) — Gilad Green, Aug 08 '16 at 12:17
I have no idea what "I want to focus on solving this type of problem here" can possibly mean because without looking at your linq it is a guessing game, there could be 10K things wrong and 20K ways to improve it. — vlscanner, Aug 08 '16 at 17:04
Off-topic, because you don't show code that demonstrates the problem. — Gert Arnold, Aug 08 '16 at 19:47
@GertArnold: Added wonderful code, especially for you. Have at it! — Protector one, Aug 12 '16 at 08:05

score 3 · Answer 1 · answered Aug 13 '16 at 22:47

One obvious way to improve your query catches the eye immediately in the first part:

var items = Db.Orders.Where(item =>
    (siteid == null || item.SiteId == siteid)
 && (ls_list.Contains(item.OrderOrderLifeCycles.OrderByDescending(it => it.Id).First().OrderLifeCycleId))
 && (item.OrderOrderPaymentStatus.Any(ops => statusPaymentSuccess.Contains(ops.OrderPaymentStatus.Code))
 && (CycleID == null || item.OrderOrderLifeCycles.First().OrderLifeCycleId == CycleID)
 && (LocationID == null || item.SaleLocationId == LocationID)
 && (string.IsNullOrEmpty(SalesPerson) || item.EmployeeName.ToLower() == SalesPerson.ToLower()))
);

Remember that the whole LINQ statement is translated into SQL, including all these null checks. This makes the SQL query needlessly complex and harder to process by the query optimizer. (By the way, you show a SQL query belonging to another LINQ statement).

The recommended way to deal with nullable conditions is to compose the query:

IQueryable<Order> items = var items = Db.Orders;

if(siteid != null)
{
    items = items.Where(item => item.SiteId == siteid);
}
if (CycleID != null)
{
    items = items.Where(item => item.OrderOrderLifeCycles.First().OrderLifeCycleId == CycleID);
}
// etc.

Another thing is

item.EmployeeName.ToLower() == SalesPerson.ToLower()

This converts the EmployeeName field values before a search condition is applied to it. This means that any index on EmployeeName can't be used (also know as not sargable). I think you can remove the ToLower() calls. In the SQL query the database collation of the EmployeeName field is used and that's very likely to be case-insensitive (CI) by default.

Finally, you may consider to execute the grouping ...

GroupBy(item => item.FirstPayDate.Value.Year + "-" + item.FirstPayDate.Value.Month)

... in memory (LINQ to objects) instead of in the database. That is:

return betweenorders.Where(item => item.FirstPayDate >= start && item.FirstPayDate < stop)
.AsEnumerable() // Switch to LINQ to objects
.GroupBy(...

A grouping is translated as ORDER BY (not GROUP BY for reasons beyond the scope of this answer) and, again, the conversions of the database field FirstPayDate disables indexes. It also makes the SQL query less complex, and it's probably not a heavy operation to do this in memory.

Wow, great insights. Thanks! But how do you know the query belongs to another LINQ expression? (It shouldn't, but I guess I could have gotten sloppy with my copy-pasting after staring intently at SQL for an entire day.) — Protector one, Aug 14 '16 at 09:09
Also, do you know a good resource to find such "recommended ways" do things in LINQ/SQL? — Protector one, Aug 15 '16 at 08:38
Wrong query -- I don't see `CycleID ` etc. in the SQL. As for documentation, places to start are here: https://msdn.microsoft.com/en-us/data/hh949853.aspx, https://www.simple-talk.com/dotnet/net-tools/entity-framework-performance-and-what-you-can-do-about-it/ — Gert Arnold, Aug 15 '16 at 15:00
The parameters that don't appear in the SQL had value null; they were not included due to the null-check and short-circuit evaluation, I believe. I verified that the generated SQL indeed belongs to the posted code. Also, the links you posted both apply to Entity Framework, so I'm not sure that's relevant to my situation. — Protector one, Aug 16 '16 at 10:07
OK, apparently, this is LINQ-to-SQL. You should always tag the kind of LINQ you're using (although the SQL would have revealed it, had I paid more attention). I didn't know that LtS deals with these null-checks that smartly. EF doesn't, so you may forget about that part. The other two tips still apply though. I hardly ever work with LtS, so I don't know about any resources. — Gert Arnold, Aug 16 '16 at 19:47

score 0 · Accepted Answer · edited May 23 '17 at 12:00

Part of the discrepancy is explained in this SA answer:

SSMS typically uses an ARITHABORT ON and code typically uses ARITHABORT OFF - this is basically an option for how to handle what happens if a mathematical line in the code has an error - e.g. divide by zero.

The main thing here, though, is that both methods have a different execution plan - which is why the same thing can (randomly) take a lot longer on the website than in SSMS.

The execution plans are compiled based on estimates of the first time it is used so what you randomly find is that the execution plan is cached in a terrible way that suits your first query but is horrible for subsequent queries. This is what happened here and it is also why it just suddenly started working again - a new query plan was created after the stored procedure was changed.

Executing the query in SSMS with SET ARITHABORT OFF already slows the query's execution a lot. It's still at least 300% faster than the LINQ-to-SQL version in code though, so I'll update this answer with more as/if I find it.

Edit: SSMS doesn't have to deal with Object Tracking like LINQ does, so when handling queries that only do reads (vs. writes), we can speedup LINQ-to-SQL execution by disabling Object Tracking. You'll then have to manually specify which objects to load though, which (as far as I'm aware) can only be determined through trial and error.
For my query, the disabling of tracking and the object loading can be handled with this code:

db.ObjectTrackingEnabled = false;
var lo = new DataLoadOptions();
lo.LoadWith<Order>(x => x.OrderOrderPaymentStatus);
lo.LoadWith<OrderOrderPaymentStatus>(x => x.OrderPaymentStatus);
db.LoadOptions = lo;

LINQ query slow, creates Timeout; generated SQL is fine?

2 Answers2