Why does this query timeout? V2

Question

This question is a followup to This Question

The solution, clearing the execution plan cache seemed to work at the time, but i've been running into the same problem over and over again, and clearing the cache no longer seems to help. There must be a deeper problem here.

I've discovered that if I remove the .Distinct() from the query, it returns rows (with duplicates) in about 2 seconds. However, with the .Distinct() it takes upwards of 4 minutes to complete. There are a lot of rows in the tables, and some of the where clause fields do not have indexes. However, the number of records returned is fairly small (a few dozen at most).

The confusing part about it is that if I get the SQL generated by the Linq query, via Linqpad, then execute that code as SQL or in SQL Management Studio (including the DISTINCT) it executes in about 3 seconds.

What is the difference between the Linq query and the executed SQL?

I have a short term workaround, and that's to return the set without .Distinct() as a List, then using .Distinct on the list, this takes about 2 seconds. However, I don't like doing SQL Server work on the web server.

I want to understand WHY the Distinct is 2 orders of magnitude slower in Linq, but not SQL.

UPDATE:

When executing the code via Linq, the sql profiler shows this code, which is basically identical query.

sp_executesql N'SELECT DISTINCT [t5].[AccountGroupID], [t5].[AccountGroup] 
    AS [AccountGroup1]
FROM [dbo].[TransmittalDetail] AS [t0]
INNER JOIN [dbo].[TransmittalHeader] AS [t1] ON [t1].[TransmittalHeaderID] = 
    [t0].[TransmittalHeaderID]
INNER JOIN [dbo].[LineItem] AS [t2] ON [t2].[LineItemID] = [t0].[LineItemID]
LEFT OUTER JOIN [dbo].[AccountType] AS [t3] ON [t3].[AccountTypeID] = 
    [t2].[AccountTypeID]
LEFT OUTER JOIN [dbo].[AccountCategory] AS [t4] ON [t4].[AccountCategoryID] = 
    [t3].[AccountCategoryID]
LEFT OUTER JOIN [dbo].[AccountGroup] AS [t5] ON [t5].[AccountGroupID] = 
    [t4].[AccountGroupID]
LEFT OUTER JOIN [dbo].[AccountSummary] AS [t6] ON [t6].[AccountSummaryID] = 
    [t5].[AccountSummaryID]
WHERE ([t1].[TransmittalEntityID] = @p0) AND ([t1].[DateRangeBeginTimeID] = @p1) AND 
([t1].[ScenarioID] = @p2) AND ([t6].[AccountSummaryID] = @p3)',N'@p0 int,@p1 int,
   @p2 int,@p3 int',@p0=196,@p1=20100101,@p2=2,@p3=0

UPDATE:

The only difference between the queries is that Linq executes it with sp_executesql and SSMS does not, otherwise the query is identical.

UPDATE:

I have tried various Transaction Isolation levels to no avail. I've also set ARITHABORT to try to force a recompile when it executes, and no difference.

Answer 1

The SQL that Linqpad gives you may not be exactly what is being sent to the DB.

Here's what I would suggest:

1. Run SQL Profiler against the DB while you execute the query. Find the statement which corresponds to your query
2. Paste the whole statment into SSMS, and enable the "Show Actual Execution Plan" option.
3. Post the resulting plan here for people to dissect.

Key things to look for:

• Table Scans, which usually imply that an index is missing
• Wide arrows in the graphical plan, indicating lots of intermediary rows being processed.

If you're using SQL 2008, viewing the plan will often tell you if there are any indexes missing which should be added to speed up the query.

Also, are you executing against a DB which is under load from other users?

Answer 2

At first glance there's a lot of joins, but I can only see one thing to reduce the number right away w/out having the schema in front of me...it doesn't look like you need AccountSummary.

[t6].[AccountSummaryID] = @p3

could be

[t5].[AccountSummaryID] = @p3

Return values are from the [t5] table. [t6] is only used filter on that one parameter which looks like it is the Foreign Key from t5 to t6, so it is present in [t5]. Therefore, you can remove the join to [t6] altogether. Or am I missing something?

Answer 3

Check that you have the same Transaction Isolation level between your SSMS session and your application. That's the biggest culprit I've seen for large performance discrepancies between identical queries.

Also, there are different connection properties in use when you work through SSMS than when executing the query from your application or from LinqPad. Do some checks into the Connection properties of your SSMS connection and the connection from your application and you should see the differences. All other things being equal, that could be the difference. Keep in mind that you are executing the query through two different applications that can have two different configurations and could even be using two different database drivers. If the queries are the same then that would be only differences I can see.

On a side note if you are hand-crafting the SQL, you may try moving the conditions from the WHERE clause into the appropriate JOIN clauses. This actually changes how SQL Server executes the query and can produce a more efficient execution plan. I've seen cases where moving the filters from the WHERE clause into the JOINs caused SQL Server to filter the table earlier in the execution plan and significantly changed the execution time.

Answer 4

Are you sure you want to use LEFT OUTER JOIN here? This query looks like it should probably be using INNER JOINs, especially because you are taking the columns that are potentially NULL and then doing a distinct on it.

Answer 5

The bad plan is most likely the result of parameter sniffing: http://blogs.msdn.com/b/queryoptteam/archive/2006/03/31/565991.aspx

Unfortunately there is not really any good universal way (that I know of) to avoid that with L2S. context.ExecuteCommand("sp_recompile ...") would be an ugly but possible workaround if the query is not executed very frequently.

Changing the query around slightly to force a recompile might be another one.

Moving parts (or all) of the query into a view*, function*, or stored procedure* DB-side would be yet another workaround.
 * = where you can use local params (func/proc) or optimizer hints (all three) to force a 'good' plan

Btw, have you tried to update statistics for the tables involved? SQL Server's auto update statistics doesn't always do the job, so unless you have a scheduled job to do that it might be worth considering scripting and scheduling update statistics... ...tweaking up and down the sample size as needed can also help.

There may be ways to solve the issue by adding* (or dropping*) the right indexes on the tables involved, but without knowing the underlying db schema, table size, data distribution etc that is a bit difficult to give any more specific advice on...
 * = Missing and/or overlapping/redundant indexes can both lead to bad execution plans.