Ideas how to make this query faster?Check existence with EXISTS outperform COUNT! … Not?Which of these...
I am a loser when it comes to jobs, what possibilities do I have?
70s or 80s B-movie about aliens in a family's television, fry the house cat and trap the son inside the TV
Did ancient Germans take pride in leaving the land untouched?
How does holding onto an active but un-used credit card affect your ability to get a loan?
Can you say "leftside right"?
Isn't a semicolon (';') needed after a function declaration in C++?
How do I avoid the "chosen hero" feeling?
Buying a "Used" Router
How can I handle players killing my NPC outside of combat?
How to purchase a drop bar bike that will be converted to flat bar?
When distributing a Linux kernel driver as source code, what's the difference between Proprietary and GPL license?
Is there any danger of my neighbor having my wife's signature?
Sed-Grep-Awk operations
What happens if both players misunderstand the game state until it's too late?
Boss asked me to sign a resignation paper without a date on it along with my new contract
Canadian Visa by land
bash aliases do not expand even with shopt expand_aliases
Graphical modeler calculator missing in QGIS3
Resorting data from a multidimensional list
What is this mysterious *green square* on my Content Editor?
How do I narratively explain how in-game circumstances do not mechanically allow a PC to instantly kill an NPC?
Is Developer Console going to be deprecated?
How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?
How to know you are over-explaining and oversimplifying a subject?
Ideas how to make this query faster?
Check existence with EXISTS outperform COUNT! … Not?Which of these queries is best for performance?Every query plan statistic says my query should be faster, but it is notWindow function vs group by method: find value for MAX IDMulti-statement TVF vs Inline TVF PerformanceOPTION (RECOMPILE) is Always Faster; Why?Aggregation in Outer Apply vs Left Join vs Derived tablesqlpackage.exe SELECT statement causing massive readsSql query performance measure IO vs TIMEHigh processor utilization when running a stored procedure
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.
sql-server sql-server-2008 execution-plan tuning
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
|
show 1 more comment
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.
sql-server sql-server-2008 execution-plan tuning
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
According to the execution planTabDruhDokZboappears 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 onTabPohybyZboziin particular are noticeably off.
– RDFozz
Oct 27 '17 at 16:33
|
show 1 more comment
2
2
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.
sql-server sql-server-2008 execution-plan tuning
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
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.
sql-server sql-server-2008 execution-plan tuning
sql-server sql-server-2008 execution-plan tuning
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
edited Oct 27 '17 at 17:24
jerik1
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
asked Oct 26 '17 at 19:26
jerik1jerik1
1558
1558
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 1 min ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
According to the execution planTabDruhDokZboappears 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 onTabPohybyZboziin particular are noticeably off.
– RDFozz
Oct 27 '17 at 16:33
|
show 1 more comment
According to the execution planTabDruhDokZboappears 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 onTabPohybyZboziin particular are noticeably off.
– RDFozz
Oct 27 '17 at 16:33
According to the execution plan
TabDruhDokZbo appears to not have a clustered index. Is that correct?– Aaron
Oct 27 '17 at 3:51
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
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
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
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
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
|
show 1 more comment
1 Answer
1
active
oldest
votes
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
)
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f189436%2fideas-how-to-make-this-query-faster%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
)
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
add a comment |
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
)
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
add a comment |
0
0
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
)
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
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
)
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
edited Oct 27 '17 at 16:10
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
answered Oct 27 '17 at 15:59
paparazzopaparazzo
4,6141230
4,6141230
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f189436%2fideas-how-to-make-this-query-faster%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
According to the execution plan
TabDruhDokZboappears 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
TabPohybyZboziin particular are noticeably off.– RDFozz
Oct 27 '17 at 16:33