SQL 服务器 : query runs very slowly (up to 20 seconds)

Question

我现在带着“我的”数据库查询的问题来到这里。它在“冷”数据库上执行大约 22-25 秒，我正在拼命寻找任何改进它的方法。

我很乐意跳过任何与表相关的建议，仅仅是因为我无法更改其结构(太糟糕了)。我已经得到了我所得到的，而且很好......我只是想找到任何解决方案来提高这个查询的性能。我知道数据库设计不佳，但目前我对此无能为力，所以如果没有办法改进查询，我会接受。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET STATISTICS PROFILE ON;

SELECT <STUFF TO SELECT>
FROM [dbo].[2009_Zlecenia] AS Z 
OUTER APPLY (SELECT TOP 1 M1.DataDo AS 'DataRozladunku', M1.Kod, M1.Miasto, MK1.Skrot FROM [dbo].[MiejscaZaladunkuRozladunku] AS M1 LEFT JOIN [dbo].[Kraje] AS MK1 ON M1.Kraj = MK1.Id WHERE M1.Zlecenie = Z.Id AND M1.Rodzaj = 2 ORDER BY Data DESC) AS MZR 
OUTER APPLY (SELECT TOP 1 M2.DataDo AS 'DataZaladunku', M2.Kod, M2.Miasto, MK2.Skrot FROM [dbo].[MiejscaZaladunkuRozladunku] AS M2 LEFT JOIN [dbo].[Kraje] AS MK2 ON M2.Kraj = MK2.Id WHERE M2.Zlecenie = Z.Id AND M2.Rodzaj = 1 ORDER BY Data ASC) AS MZR1 
OUTER APPLY (Select count(FP1.Id) 'Count' FROM [dbo].[2009_FakturyPrzewoznika] AS FP1 WHERE FP1.ZlecenieId = Z.Id group by FP1.ZlecenieId) AS FP 
OUTER APPLY (SELECT count(FP3.ZlecenieId) 'Count' FROM [dbo].[2009_FakturyPrzewoznika] AS FP3 WHERE FP3.ZlecenieId IN (Select Id FROM [dbo].[2009_Zlecenia] WHERE IdZlecenieNadrzedne <> 0 And IdZlecenieNadrzedne = Z.Id) GROUP BY FP3.ZlecenieId) AS FP2 
OUTER APPLY (SELECT TOP 1 Nr FROM [dbo].[2009_KartyDrogowe] AS KD1 LEFT JOIN [dbo].[ZleceniaKartyDrogowej] AS ZKD1 ON ZKD1.KartaDrogowa = KD1.Id WHERE ZKD1.Zlecenie = Z.Id) AS KD 
OUTER APPLY ( Select count(Id) 'Count' FROM [dbo].[2009_Zlecenia] WHERE IdZlecenieNadrzedne <> 0 And IdZlecenieNadrzedne = Z.Id) AS ZP 
LEFT JOIN [dbo].[ZleceniaWalutaObca] AS ZWO ON Z.Id = ZWO.OrderId 
LEFT JOIN [dbo].[Kraje] AS K1 ON Z.TransportZ = K1.Id 
LEFT JOIN [dbo].[Kraje] AS K2 ON Z.TransportDo = K2.Id 
LEFT JOIN [dbo].[Lista] AS L1 ON Z.Status = L1.Id 
LEFT JOIN [dbo].[Uzytkownicy] AS U ON Z.Uzytkownik = U.Id 
LEFT JOIN [dbo].[Oddzialy] AS UO ON U.Oddzial = UO.Id 
LEFT JOIN [dbo].[FakturyZlecen] AS FZ ON FZ.Zlecenie = Z.Id 
LEFT JOIN [dbo].[FakturyZlecen] AS FZ1 ON FZ1.Zlecenie = Z.IdZlecenieNadrzedne 
LEFT JOIN [dbo].[2009_Faktury] AS F1 ON FZ.Faktura = F1.Id 
LEFT JOIN [dbo].[2009_Faktury] AS F2 ON FZ1.Faktura = F2.Id 
LEFT JOIN [dbo].[Firmy] AS FO ON FO.Id = Z.ZleceniodawcaId 
LEFT JOIN [dbo].[Uzytkownicy] AS O1 ON FO.Opiekun1 = O1.Id 
LEFT JOIN [dbo].[Uzytkownicy] AS O2 ON FO.Opiekun2 = O2.Id 
LEFT JOIN [dbo].[Uzytkownicy] AS O3 ON FO.Opiekun3 = O3.Id 
WHERE Z.TypZlecenia = 4 AND Z.Importowane=0 ORDER BY YEAR(Z.DataZlecenia) DESC, Z.Idx DESC, Z.Nr DESC


SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
SET STATISTICS PROFILE OFF;

我会发布执行计划，但它相当大。我会敏锐地回答有关它的任何问题! :)

大约 80% 的查询时间都花在了外部应用子句中的排序上。

以下是在“热”服务器上执行的统计数据:

(16467 row(s) affected)

Table 'Uzytkownicy'. Scan count 0, logical reads 33042, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Firmy'. Scan count 0, logical reads 50421, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '2009_Faktury'. Scan count 0, logical reads 48577, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'FakturyZlecen'. Scan count 32934, logical reads 101846, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Oddzialy'. Scan count 1, logical reads 32935, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Lista'. Scan count 0, logical reads 32934, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Kraje'. Scan count 2, logical reads 65874, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ZleceniaWalutaObca'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 65420, logical reads 450989, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '2009_Zlecenia'. Scan count 32635, logical reads 84027, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'ZleceniaKartyDrogowej'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table '2009_FakturyPrzewoznika'. Scan count 318, logical reads 687, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'MiejscaZaladunkuRozladunku'. Scan count 2, logical reads 5670, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times: CPU time = 1547 ms, elapsed time = 1771 ms.

我强调了“工作表”，因为我认为这是表现如此糟糕的主要原因。

有什么有用的建议吗？

@编辑

执行计划在这里:

Answer 1

该计划似乎包含很多index spools，它是SQL Server 为tempdb 建立临时索引的操作符。至少对于那些情况，永久索引应该会大大提高性能。

执行select count(column) 时，SQL Server 计算该列的非空值。当使用 select count(*) 时，会计算行数，SQL Server 可以对任何索引进行索引扫描。

最好从计划中检查键查找，如果在实际执行计数很高的地方存在此类查找，则将这些列添加为索引中的包含字段会删除键查找。这会产生插入/更新的额外费用。

还将一个大查询分解成更小的部分可以帮助优化器选择更好的计划。在具有多个大表的查询中，查询计划创建也可能以超时结束，从而导致非常糟糕的计划。这可以在查询计划的第一个节点(“优化级别”)的属性中看到