2

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.
  • According to the execution plan TabDruhDokZbo appears to not have a clustered index. Is that correct? – Aaron Oct 27 '17 at 3:51
  • Yes, it is. It's a heap with just a few rows. – jerik1 Oct 27 '17 at 4:38
  • What query? There is not query in the question. – paparazzo Oct 27 '17 at 13:36
  • Paparazzi: the query is in the plan; I'm running MS SQL Server 2008 SP3, thanks – jerik1 Oct 27 '17 at 14:27
  • How often are the statistics updated? it looks like the estimated rows on TabPohybyZbozi in particular are noticeably off. – RDFozz Oct 27 '17 at 16:33
0

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
                 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.