Why choose Top X Kill Performance

Question

I have a T-SQL query below (generated by linq-to-sql, so it looks a bit strange.) When I delete the top ten, it works in less than 1 second. When I leave the top ten, it takes 68 seconds.

Without the top 10, the total number of lines is only 19.

Why are the top 10 destroying performance in this situation?

SELECT TOP (10) [t3].[value] AS [StartDate], [t3].[AH_TimeStamp4] AS [EndDate], [t3].[value2] AS [RunTime], [t3].[AH_Idnr] AS [RunNumber], [t3].[AH_Status] AS [StatusCode], [t3].[STATUS_DESC] AS [StatusDesc], [t3].[STATUS_TYPE] AS [StatusType] FROM ( SELECT COALESCE([t1].[AH_TimeStamp2],[t1].[AH_TimeStamp1]) AS [value], [t1].[AH_TimeStamp4], CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]), [t1].[AH_TimeStamp1]), [t1].[AH_TimeStamp4])) * 10000) AS [value2], [t1].[AH_Idnr], [t1].[AH_Status], [t2].[STATUS_DESC], [t2].[STATUS_TYPE], [t0].[OH_Name] FROM [dbo].[OH] AS [t0] INNER JOIN [dbo].[AH] AS [t1] ON ([t0].[OH_Idnr]) = [t1].[AH_OH_Idnr] INNER JOIN [dbo].[CHK_JOB_STATUS_CODE] AS [t2] ON [t1].[AH_Status] = ([t2].[STATUS_CODE]) ) AS [t3] WHERE [t3].[OH_Name] = @p0 ORDER BY [t3].[AH_Idnr] DESC 

Edit → as per request, here created linq request without Take (10)

 SELECT [t3].[value] AS [StartDate], [t3].[AH_TimeStamp4] AS [EndDate], [t3].[value2] AS [RunTime], [t3].[AH_Idnr] AS [RunNumber], [t3].[AH_Status] AS [StatusCode], [t3].[STATUS_DESC] AS [StatusDesc], [t3].[STATUS_TYPE] AS [StatusType] FROM ( SELECT COALESCE([t1].[AH_TimeStamp2],[t1].[AH_TimeStamp1]) AS [value], [t1].[AH_TimeStamp4], CONVERT(BigInt,(((CONVERT(BigInt,DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]))) * 86400000) + DATEDIFF(MILLISECOND, DATEADD(DAY, DATEDIFF(DAY, [t1].[AH_TimeStamp1], [t1].[AH_TimeStamp4]), [t1].[AH_TimeStamp1]), [t1].[AH_TimeStamp4])) * 10000) AS [value2], [t1].[AH_Idnr], [t1].[AH_Status], [t2].[STATUS_DESC], [t2].[STATUS_TYPE], [t0].[OH_Name] FROM [dbo].[OH] AS [t0] INNER JOIN [dbo].[AH] AS [t1] ON ([t0].[OH_Idnr]) = [t1].[AH_OH_Idnr] INNER JOIN [dbo].[CHK_JOB_STATUS_CODE] AS [t2] ON [t1].[AH_Status] = ([t2].[STATUS_CODE]) ) AS [t3] WHERE [t3].[OH_Name] = @p0 ORDER BY [t3].[AH_Idnr] DESC 

Edit 2 -> here LINQ query - nothing special, only 3 joins

 List<UC4Status> statusList = uc4DB.OHs.Where(o => o.OH_Name == jobName).Join(uc4DB.AHs, oh => oh.OH_Idnr, ah => ah.AH_OH_Idnr, (oh, ah) => ah) .Join(uc4DB.CHK_JOB_STATUS_CODEs, ah => ah.AH_Status, job => job.STATUS_CODE, (a, s) => new UC4Status { StartDate = a.AH_TimeStamp2 ?? a.AH_TimeStamp1, EndDate = a.AH_TimeStamp4, RunTime = a.AH_TimeStamp4 - a.AH_TimeStamp1, StatusType = s.STATUS_TYPE, StatusDesc = s.STATUS_DESC, StatusCode = a.AH_Status, RunNumber = a.AH_Idnr }).OrderByDescending(r => r.RunNumber).Take(maxResults).ToList(); 

Answer 1

More important than the number of rows returned is the number of rows in each joined table. Without access to the query plan, it’s hard to say what the problem is. But the problem may be due to outdated or missing column statistics combined with the lack of good indexes, as a result of which the query optimizer makes a very bad decision when using top . For example, if the optimizer incorrectly makes the assumption that the top operator will work only with a small number of rows in each joined table, it may choose to use inner-cycle joins with viewing tables on each joined table, which can be terribly slow on a large number of records (but can work very effectively when working with multiple records). In the case without top optimizer can correctly assume that he will have to scan a large amount of records in the joined tables and use the approach that is better suited for such an operation, such as a hash match, to join the table scan.

You can try updating the statistics and adding other indexes to see if this helps.

Answer 2

How many rows without the top 10? Are there any indexes? I would suggest that this is due to the subqueries connecting t0, t1 and t2 ... you can try something like

 select top 10 * from (
   select t3.value ....
   from ( 
     select coalesce ...)
   where ... order by ... 
 )