title: SQL Server : query runs very slowly (up to 20 seconds)
body: <p>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.</p>
  
  <p>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.</p>
  
  <pre><code>SET STATISTICS IO ON;
  SET STATISTICS TIME ON;
  SET STATISTICS PROFILE ON;
  
  SELECT &lt;STUFF TO SELECT&gt;
  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 &lt;&gt; 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 &lt;&gt; 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;
  </code></pre>
  
  <p>I would post execution plan, but it's quite big. I will answer keenly any questions regarding it tho! :)</p>
  
  <p>About 80% of query time is consumed by sorting within outer apply clauses.</p>
  
  <p>Here are statistics from execution on "hot" server:</p>
  
  <blockquote>
    <p>(16467 row(s) affected) </p>
    
    <p>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. </p>
    
    <p>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.</p>
    
    <p>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. </p>
    
    <p>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. </p>
    
    <p>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. </p>
    
    <p>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. </p>
    
    <p>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. </p>
    
    <p>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. </p>
    
    <p>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. </p>
    
    <p>Table <strong>'Worktable'</strong>. Scan count 65420, <strong>logical reads</strong>
    <strong>450989</strong>, physical reads 0, read-ahead reads 0, lob logical reads 0, lob
    physical reads 0, lob read-ahead reads 0. </p>
    
    <p>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.</p>
    
    <p>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. </p>
    
    <p>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. </p>
    
    <p>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.</p>
    
    <p>SQL Server Execution Times:    CPU time = 1547 ms,  elapsed time =
    1771 ms.</p>
  </blockquote>
  
  <p>I highlighted the "worktable" as I believe that's the main reason for such a bad performance.</p>
  
  <p>Any suggestions that would help?? </p>
  
  <p>@EDIT</p>
  
  <p>Execution Plan is here:</p>
  
  <p><img src="https://i.stack.imgur.com/RbTO3.png" alt="Overview">
  <img src="https://i.stack.imgur.com/VWQhd.png" alt="ExecPlan">
  <img src="https://i.stack.imgur.com/IkQ5x.png" alt="ExecPlan2">
  <img src="https://i.stack.imgur.com/I6grL.png" alt="ExecPlan3"></p>