sql - 为什么选择Top X Kill Performance

Question

我有下面的 T-SQL 查询(由 linq-to-sql 生成，所以看起来有点奇怪。)当我删除前 10 个时，它在 1 秒内运行。当我离开前 10 名时，它需要 68 秒。

如果没有前 10 名，总行数只有 19。

为什么在这种情况下，前 10 名对性能的破坏如此之大？

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

编辑 -> 根据要求，这是不带 Take(10) 的 linq 生成的查询

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

编辑 2 -> 这是 LINQ 查询 - 没什么特别的，只有 3 个连接

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

比返回的行数更重要的是连接的每个表中的行数。如果无法访问查询计划，则很难说出问题所在。但是，该问题可能是由于过时或丢失的列统计信息以及缺少好的索引导致查询优化器在 top 时做出非常糟糕的决定。用来。例如，如果优化器错误地假设 top语句只会对每个连接表中的少量行进行操作，它可以选择使用内循环连接和每个连接表上的表扫描——这对于大量记录来说可能是一个非常缓慢的操作(但可以非常有效)仅处理少量记录时有效)。在没有 top 的情况下，优化器可以正确地假设它将必须扫描连接表中的大量记录，并使用更适合此类操作的方法，例如来自表扫描的哈希匹配连接。

您可以尝试更新统计信息并添加其他索引以查看是否有帮助。