Ideas how to make this query faster?

Question

I have the following query:

SELECT TabPohybyZbozi.ID,
       TabPohybyZbozi.NazevSozNa2,
       TabZakazka.Nazev,
       TabDokladyZbozi.DatPorizeni,
       TabPohybyZbozi.IDDoklad
FROM   TabPohybyZbozi
       INNER JOIN TabDokladyZbozi
               ON TabDokladyZbozi.ID = TabPohybyZbozi.IDDoklad
       LEFT JOIN TabZakazka
              ON TabZakazka.CisloZakazky = TabPohybyZbozi.CisloZakazky
       LEFT JOIN TabPohybyZbozi_Ext
              ON TabPohybyZbozi_Ext.ID = TabPohybyZbozi.ID
       LEFT JOIN TabDruhDokZbo
              ON TabDruhDokZbo.DruhPohybuZbo = TabDokladyZbozi.DruhPohybuZbo
                 AND TabDruhDokZbo.RadaDokladu = TabDokladyZbozi.RadaDokladu
       LEFT JOIN TabDruhDokZbo_Ext
              ON TabDruhDokZbo.ID = TabDruhDokZbo_Ext.ID
WHERE  TabDokladyZbozi.DruhPohybuZbo IN ( 13, 14, 18, 19 )
       AND NOT EXISTS(SELECT *
                      FROM   SA_NapZisk_Popis2Zmeny
                      WHERE  ( Zpracovano <> 1
                                OR Zpracovano IS NULL )
                             AND IDPolozka = TabPohybyZbozi.ID)
       AND ( ( TabPohybyZbozi.NazevSozNa2 <> TabZakazka.Nazev )
              OR ( TabPohybyZbozi.NazevSozNa2 = N''
                   AND TabZakazka.Nazev IS NULL ) )
       AND TabPohybyZbozi.NazevSozNa2 <> N''
       AND TabPohybyZbozi.NazevSozNa2 <> N'0'
       AND TabDokladyZbozi.DatPorizeni >= DATEADD(DAY, -365, GETDATE())
       AND TabDokladyZbozi.DatPorizeni <= GETDATE()
       AND ( TabDruhDokZbo_EXT._SA_ZalohovaFa = 0
              OR TabDruhDokZbo_EXT._SA_ZalohovaFa IS NULL ) 

Here is the plan: https://www.brentozar.com/pastetheplan/?id=H1taN31A-

Here are the stats:

(0 row(s) affected)
Table 'TabDruhDokZbo'. Scan count 9, logical reads 247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabDruhDokZbo_EXT'. Scan count 0, logical reads 506, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SA_NapZisk_Popis2Zmeny'. Scan count 9, logical reads 15522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabDokladyZbozi'. Scan count 9, logical reads 183743, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabPohybyZbozi'. Scan count 9, logical reads 181282, 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 'TabZakazka'. Scan count 9, logical reads 20096, 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.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 19566 ms,  elapsed time = 3590 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

There are too many rows involved, and I have no idea how to pre-filter them. The query is inside of a stored procedure, so if there is a way to re-write it not using one single query and make it faster, go ahaed.

I'm running SQL 2008 SP3

-- EDIT --

I rewrite it (thanks Paparazzi) so there are more conditions on JOIN clause and it changes the plan. And I get 1 second down. Wonder if it could be even less :-)

My query now:

SELECT TabPohybyZbozi.ID
      ,TabPohybyZbozi.NazevSozNa2
      ,TabZakazka.Nazev
      ,TabDokladyZbozi.DatPorizeni
      ,TabPohybyZbozi.IDDoklad
FROM TabPohybyZbozi 
INNER JOIN TabDokladyZbozi 
        ON TabDokladyZbozi.ID          = TabPohybyZbozi.IDDoklad
       AND TabDokladyZbozi.DruhPohybuZbo IN (13,14,18,19)
       AND TabDokladyZbozi.DatPorizeni >= DATEADD(DAY,-365,GETDATE()) 
       AND TabDokladyZbozi.DatPorizeni <= GETDATE()
INNER JOIN TabDruhDokZbo 
        ON TabDruhDokZbo.DruhPohybuZbo = TabDokladyZbozi.DruhPohybuZbo 
       AND TabDruhDokZbo.RadaDokladu   = TabDokladyZbozi.RadaDokladu
INNER JOIN TabDruhDokZbo_Ext 
        ON TabDruhDokZbo.ID            = TabDruhDokZbo_Ext.ID
       AND (TabDruhDokZbo_EXT._SA_ZalohovaFa = 0 
            OR TabDruhDokZbo_EXT._SA_ZalohovaFa IS NULL)
LEFT  JOIN TabZakazka 
        ON TabZakazka.CisloZakazky     = TabPohybyZbozi.CisloZakazky
WHERE (TabPohybyZbozi.NazevSozNa2 <> TabZakazka.Nazev 
            OR (TabPohybyZbozi.NazevSozNa2 = N'' AND TabZakazka.Nazev IS NULL))
    AND TabPohybyZbozi.NazevSozNa2 <> N'' 
    AND TabPohybyZbozi.NazevSozNa2 <> N'0'
    AND NOT EXISTS (SELECT * 
                  FROM SA_NapZisk_Popis2Zmeny 
                  WHERE (Zpracovano <> 1 OR Zpracovano IS NULL) 
                  AND IDPolozka = TabPohybyZbozi.ID)

The plan: https://www.brentozar.com/pastetheplan/?id=BysZM1W0b

Stats:

(0 row(s) affected)
Table 'SA_NapZisk_Popis2Zmeny'. Scan count 9, logical reads 15522, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabDruhDokZbo'. Scan count 9, logical reads 247, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabDruhDokZbo_EXT'. Scan count 0, logical reads 506, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabDokladyZbozi'. Scan count 9, logical reads 183743, 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 'TabPohybyZbozi'. Scan count 9, logical reads 183287, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TabZakazka'. Scan count 9, logical reads 20096, 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.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 13522 ms,  elapsed time = 2341 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

Answer 1

The last two left joins are not used and should be deleted

Have index on all ON conditions

I would try materialize in a #temp with a PK

SELECT IDPolozka 
FROM SA_NapZisk_Popis2Zmeny 
WHERE Zpracovano <> 1 OR Zpracovano IS NULL 

-

SELECT TabPohybyZbozi.ID
      ,TabPohybyZbozi.NazevSozNa2
      ,TabZakazka.Nazev
      ,TabDokladyZbozi.DatPorizeni
      ,TabPohybyZbozi.IDDoklad
FROM TabPohybyZbozi 
INNER JOIN TabDokladyZbozi 
        ON TabDokladyZbozi.ID          = TabPohybyZbozi.IDDoklad
       AND TabDokladyZbozi.DruhPohybuZbo IN (13,14,18,19)
       AND TabDokladyZbozi.DatPorizeni >= DATEADD(DAY,-365,GETDATE()) 
       AND TabDokladyZbozi.DatPorizeni <= GETDATE()
LEFT  JOIN TabZakazka 
        ON TabZakazka.CisloZakazky     = TabPohybyZbozi.CisloZakazky
LEFT  JOIN TabPohybyZbozi_Ext 
        ON TabPohybyZbozi_Ext.ID       = TabPohybyZbozi.ID 
       AND (    TabPohybyZbozi.NazevSozNa2 <> TabZakazka.Nazev 
            OR (TabPohybyZbozi.NazevSozNa2 = N'' AND TabZakazka.Nazev IS NULL))
       AND TabPohybyZbozi.NazevSozNa2 <> N'' 
       AND TabPohybyZbozi.NazevSozNa2 <> N'0'
LEFT  JOIN TabDruhDokZbo 
        ON TabDruhDokZbo.DruhPohybuZbo = TabDokladyZbozi.DruhPohybuZbo 
       AND TabDruhDokZbo.RadaDokladu   = TabDokladyZbozi.RadaDokladu
LEFT  JOIN TabDruhDokZbo_Ext 
        ON TabDruhDokZbo.ID            = TabDruhDokZbo_Ext.ID
       AND (   TabDruhDokZbo_EXT._SA_ZalohovaFa = 0 
            OR TabDruhDokZbo_EXT._SA_ZalohovaFa IS NULL)
WHERE NOT EXISTS (SELECT * 
                  FROM SA_NapZisk_Popis2Zmeny 
                  WHERE (Zpracovano <> 1 OR Zpracovano IS NULL) 
                  AND IDPolozka = TabPohybyZbozi.ID
                 )