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

1 Answer 1

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
                 )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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