SQL Server : query runs very slowly (up to 20 seconds)

Question

I come here now with a problem with "my" database query. It executes for about 22-25 seconds on "cold" database and I'm desperately looking for any way to improve it.

I'd love to skip any table-related suggestions simply because I can't change its structure (too bad). I have what I've been given and well... I'm just trying to find any solution to improve the performance of this query. I'm aware the database is not well-designed, but at the moment I can't do nothing about that, so I'll accept if there's no way to improve the query.

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;

I would post execution plan, but it's quite big. I will answer keenly any questions regarding it tho! :)

About 80% of query time is consumed by sorting within outer apply clauses.

Here are statistics from execution on "hot" server:

(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.

I highlighted the "worktable" as I believe that's the main reason for such a bad performance.

Any suggestions that would help??

@EDIT

Execution Plan is here:

Overview ExecPlan ExecPlan2 ExecPlan3

The execution plan would be nice though, maybe use SQL Sentry Plan Explorer for it to display it? — NickyvV
Worktables are temporary tables created by SQL Server on tempdb to perform tempory work - ORDER BY, GROUP BY etc. See MSDN. My guess with your query is the series of OUTER APPLY statements. If you post the execution plan we can see if there are any obvious bottlenecks regarding missing indexes etc — Paddy
Have you tried to fetch the data in smaller parts? Since you have that many tables in the select optimizer might not be able to get the best plan (the plan can even timeout, you can check that from the first node of actual plan) — James Z
Lets consider just the first two OUTER APPLY (in an effort to write this in a different way). This implies that the set inside the outer apply returns more than one row. Is this because there is a one-many between 2009_Zlecenia and MiejscaZaladunkuRozladunku or is there a one to many between MiejscaZaladunkuRozladunku and Kraje.. or both? Also have you tried ordering by just Z.DataZlecenia instead of YEAR(Z.DataZlecenia) ? — Nick.McDermaid

James Z James Z · Accepted Answer · 2015-03-12T13:41:46

The plan seem to contain a lot of index spools, which is an operator where SQL Server builds a temporary index into tempdb. At least for those cases permanent index should improve the performance a lot.

When doing select count(column) SQL Server calculates the non-null values of that column. When select count(*) is used then the number of rows is calculated and SQL Server can just do an index scan for any index.

It is good to check key lookups from the plan, and if there are such lookups where actual execution count is high, adding those columns as an included field in the index removes the key lookup. This has additional cost for inserts / updates.

Also breaking the one big query into smaller pieces can help the optimizer to choose a better plan. In queries with several big tables it is also possible that the query plan creation ends in a timeout causing really bad plans. This can be seen in the properties of the first node of the query plan ("Optimization level")

SQL Server : query runs very slowly (up to 20 seconds)

2 Answers