MS SQL Server - NOT EXISTS bad performance

Author

NOT EXISTS bad performance

Patrick

2005-09-29, 9:23 am

Hi,

We just upgraded from SQLServer 7.0 to 2000. Now we have a SQL statement
which runs very long (10-20 hours!) compared to before (about 10 seconds).
The statement joins three tables and filters out historicized data. It
contains a NOT EXISTS clause for every table. If NOT EXISTS is the last WHERE
clause for every table (as it is currently implemented, see example below),
SQLServer 2000 uses a LOOP JOIN to join the table, which leads to this very
bad performance. If NOT EXISTS is not the last where clause, it uses a HASH
or MERGE JOIN, and it is running fine like in SQLServer 7.0. I cant see any
reason why it does this.

Maybe it is related to
http://support.microsoft.com/?scid=...15593&x=6&y=12, so it could
be a bug? But I tested with SP4 and SP3 and get the same (bad) results. With
SP2 it also changes the executation plan when I reorder the statements, but
it never uses a LOOP join, so performance is always good. Any ideas?

Because we have a lot of these WHERE statements, I would prefere to have a
SQLServer based solution instead of having to check and change every
statement in our application.

Regards,
Patrick

Examples:

Current implementation:

SELECT count(*) FROM Table1 table1
INNER JOIN Table2 table2 ON (table1.table2 = table2.id)
INNER JOIN Table3 table3 ON (table2.table3 = table3.id)
WHERE table3.oid = '3EAAF811-C8CB-11D3-9479-00600845FDA2'
AND table1.gueltigAb <= '27/9/2005' AND ...
AND table1.historisierung = (... )
AND NOT EXISTS (select id FROM Table1 tableAlias2 WHERE ... )
AND table2.gueltigAb <= '27/9/2005' AND ...
AND table2.historisierung = (... )
AND NOT EXISTS (select id FROM Table2 tableAlias2 WHERE ... )
AND table3.gueltigAb <= '27/9/2005' AND ...
AND table3.historisierung = (... )
AND NOT EXISTS (select id FROM Table3 tableAlias2 WHERE ... )

|--Compute Scalar(DEFINE:(& #91;Expr1015]=Conver
t([Expr1024])))
|--Stream Aggregate(DEFINE:(& #91;Expr1024]=Count(
*)))
|--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[table3]))
|--Nested Loops(Inner Join, OUTER
REFERENCES:([table1].[table2]))
| |--Nested Loops(Left Anti Semi Join, OUTER
REFERENCES:([table1].[id]))
| | |--Sort(ORDER BY:([table1].[id] ASC))
....
==>> Very bad performance!

Change NOT EXISTS not be last where statement of tables:

SELECT count(*) FROM Table1 table1
INNER JOIN Table2 table2 ON (table1.table2 = table2.id)
INNER JOIN Table3 table3 ON (table2.table3 = table3.id)
WHERE table3.oid = '3EAAF811-C8CB-11D3-9479-00600845FDA2'
AND table1.gueltigAb <= '27/9/2005' AND ...
AND NOT EXISTS (select id FROM Table1 tableAlias2 WHERE ... )
AND table1.historisierung = (... )
AND table2.gueltigAb <= '27/9/2005' AND ...
AND NOT EXISTS (select id FROM Table2 tableAlias2 WHERE ... )
AND table2.historisierung = (... )
AND table3.gueltigAb <= '27/9/2005' AND ...
AND NOT EXISTS (select id FROM Table3 tableAlias2 WHERE ... )
AND table3.historisierung = (... )

|--Compute Scalar(DEFINE:(& #91;Expr1015]=Conver
t([Expr1041])))
|--Stream Aggregate(DEFINE:(& #91;Expr1041]=Count(
*)))
|--Hash Match(Inner Join, HASH:([table3].[id],
[table3].& #91;historisierung])
=([ST].[id], [Expr1009]),
RESIDUAL:([ST].[id]=[table3].[id] AND [table3].& #91;historisierung]=
[Expr1009]))
|--Nested Loops(Left Anti Semi Join, OUTER
REFERENCES:([table3].[id]))
| |--Clustered Index
Scan(OBJECT:([VE2000].[VE2000].[Table3].[PK_Table3_23] AS [table3]),
WHERE:(([table3].[gueltigAb]<='Sep 27 2005 12:00AM' AND
[table3].[gueltigBis]>='Sep 27 2005 12:00AM') AND
[table3].[ve]='3EAAF811-C8CB-11D3-9479-00600845FDA2'))
| |--Row Count Spool
| |--Top(1)
| |--Clustered Index
Seek(OBJECT:([VE2000].[VE2000].[Table3].[PK_Table3_23] AS [tableAlias2]),
SEEK:([tableAlias2].[id]=[table3].[id]),
WHERE:(Convert([tableAlias2].[geloescht])=1) ORDERED FORWARD)
|--Merge Join(Inner Join,
MERGE:([ST].[id])=([table2].[table3]), RESIDUAL:([table2].[table3]=[ST].[id]))
....
==>> Performance is ok!

Instead of reordering WHERE clauses, using INNER HASH JOIN also leads to
better performance.

Gert-Jan Strik

2005-09-29, 8:23 pm

As the very first step: make sure the statistics are up to date. When in
doubt, run UPDATE STATISTICS, preferably with FULLSCAN.

Hope this helps,
Gert-Jan

Patrick wrote:
>
> Hi,
>
> We just upgraded from SQLServer 7.0 to 2000. Now we have a SQL statement
> which runs very long (10-20 hours!) compared to before (about 10 seconds).
> The statement joins three tables and filters out historicized data. It
> contains a NOT EXISTS clause for every table. If NOT EXISTS is the last WHERE
> clause for every table (as it is currently implemented, see example below),
> SQLServer 2000 uses a LOOP JOIN to join the table, which leads to this very
> bad performance. If NOT EXISTS is not the last where clause, it uses a HASH
> or MERGE JOIN, and it is running fine like in SQLServer 7.0. I cant see any
> reason why it does this.
>
> Maybe it is related to
> http://support.microsoft.com/?scid=...15593&x=6&y=12, so it could
> be a bug? But I tested with SP4 and SP3 and get the same (bad) results. With
> SP2 it also changes the executation plan when I reorder the statements, but
> it never uses a LOOP join, so performance is always good. Any ideas?
>
> Because we have a lot of these WHERE statements, I would prefere to have a
> SQLServer based solution instead of having to check and change every
> statement in our application.
>
> Regards,
> Patrick
>
> Examples:
>
> Current implementation:
>
> SELECT count(*) FROM Table1 table1
> INNER JOIN Table2 table2 ON (table1.table2 = table2.id)
> INNER JOIN Table3 table3 ON (table2.table3 = table3.id)
> WHERE table3.oid = '3EAAF811-C8CB-11D3-9479-00600845FDA2'
> AND table1.gueltigAb <= '27/9/2005' AND ...
> AND table1.historisierung = (... )
> AND NOT EXISTS (select id FROM Table1 tableAlias2 WHERE ... )
> AND table2.gueltigAb <= '27/9/2005' AND ...
> AND table2.historisierung = (... )
> AND NOT EXISTS (select id FROM Table2 tableAlias2 WHERE ... )
> AND table3.gueltigAb <= '27/9/2005' AND ...
> AND table3.historisierung = (... )
> AND NOT EXISTS (select id FROM Table3 tableAlias2 WHERE ... )
>
> |--Compute Scalar(DEFINE:(& #91;Expr1015]=Conver
t([Expr1024])))
> |--Stream Aggregate(DEFINE:(& #91;Expr1024]=Count(
*)))
> |--Nested Loops(Inner Join, OUTER REFERENCES:([table2].[table3]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([table1].[table2]))
> | |--Nested Loops(Left Anti Semi Join, OUTER
> REFERENCES:([table1].[id]))
> | | |--Sort(ORDER BY:([table1].[id] ASC))
> ...
> ==>> Very bad performance!
>
> Change NOT EXISTS not be last where statement of tables:
>
> SELECT count(*) FROM Table1 table1
> INNER JOIN Table2 table2 ON (table1.table2 = table2.id)
> INNER JOIN Table3 table3 ON (table2.table3 = table3.id)
> WHERE table3.oid = '3EAAF811-C8CB-11D3-9479-00600845FDA2'
> AND table1.gueltigAb <= '27/9/2005' AND ...
> AND NOT EXISTS (select id FROM Table1 tableAlias2 WHERE ... )
> AND table1.historisierung = (... )
> AND table2.gueltigAb <= '27/9/2005' AND ...
> AND NOT EXISTS (select id FROM Table2 tableAlias2 WHERE ... )
> AND table2.historisierung = (... )
> AND table3.gueltigAb <= '27/9/2005' AND ...
> AND NOT EXISTS (select id FROM Table3 tableAlias2 WHERE ... )
> AND table3.historisierung = (... )
>
> |--Compute Scalar(DEFINE:(& #91;Expr1015]=Conver
t([Expr1041])))
> |--Stream Aggregate(DEFINE:(& #91;Expr1041]=Count(
*)))
> |--Hash Match(Inner Join, HASH:([table3].[id],
> [table3].& #91;historisierung])
=([ST].[id], [Expr1009]),
> RESIDUAL:([ST].[id]=[table3].[id] AND [table3].& #91;historisierung]=
[Expr1009]))
> |--Nested Loops(Left Anti Semi Join, OUTER
> REFERENCES:([table3].[id]))
> | |--Clustered Index
> Scan(OBJECT:([VE2000].[VE2000].[Table3].[PK_Table3_23] AS [table3]),
> WHERE:(([table3].[gueltigAb]<='Sep 27 2005 12:00AM' AND
> [table3].[gueltigBis]>='Sep 27 2005 12:00AM') AND
> [table3].[ve]='3EAAF811-C8CB-11D3-9479-00600845FDA2'))
> | |--Row Count Spool
> | |--Top(1)
> | |--Clustered Index
> Seek(OBJECT:([VE2000].[VE2000].[Table3].[PK_Table3_23] AS [tableAlias2]),
> SEEK:([tableAlias2].[id]=[table3].[id]),
> WHERE:(Convert([tableAlias2].[geloescht])=1) ORDERED FORWARD)
> |--Merge Join(Inner Join,
> MERGE:([ST].[id])=([table2].[table3]), RESIDUAL:([table2].[table3]=[ST].[id]))
> ...
> ==>> Performance is ok!
>
> Instead of reordering WHERE clauses, using INNER HASH JOIN also leads to
> better performance.

Jerry Spivey

2005-09-30, 3:23 am

Patrick,

Do you have indexes for the columns listed in the WHERE clause? Are the
statistics for these indexes current and up-to-date? Have you run DBCC
SHOWCONTIG to look for possible fragmentation?

HTH

Jerry
"Patrick" <Patrick@discussions.microsoft.com> wrote in message
news:8C18E099-EC93-4607-B345- 1730B3011A05@microso
ft.com...
> Hi,
>
> We just upgraded from SQLServer 7.0 to 2000. Now we have a SQL statement
> which runs very long (10-20 hours!) compared to before (about 10 seconds).
> The statement joins three tables and filters out historicized data. It
> contains a NOT EXISTS clause for every table. If NOT EXISTS is the last
> WHERE
> clause for every table (as it is currently implemented, see example
> below),
> SQLServer 2000 uses a LOOP JOIN to join the table, which leads to this
> very
> bad performance. If NOT EXISTS is not the last where clause, it uses a
> HASH
> or MERGE JOIN, and it is running fine like in SQLServer 7.0. I cant see
> any
> reason why it does this.
>
> Maybe it is related to
> http://support.microsoft.com/?scid=...15593&x=6&y=12, so it
> could
> be a bug? But I tested with SP4 and SP3 and get the same (bad) results.
> With
> SP2 it also changes the executation plan when I reorder the statements,
> but
> it never uses a LOOP join, so performance is always good. Any ideas?
>
> Because we have a lot of these WHERE statements, I would prefere to have a
> SQLServer based solution instead of having to check and change every
> statement in our application.
>
> Regards,
> Patrick
>
> Examples:
>
> Current implementation:
>
> SELECT count(*) FROM Table1 table1
> INNER JOIN Table2 table2 ON (table1.table2 = table2.id)
> INNER JOIN Table3 table3 ON (table2.table3 = table3.id)
> WHERE table3.oid = '3EAAF811-C8CB-11D3-9479-00600845FDA2'
> AND table1.gueltigAb <= '27/9/2005' AND ...
> AND table1.historisierung = (... )
> AND NOT EXISTS (select id FROM Table1 tableAlias2 WHERE ... )
> AND table2.gueltigAb <= '27/9/2005' AND ...
> AND table2.historisierung = (... )
> AND NOT EXISTS (select id FROM Table2 tableAlias2 WHERE ... )
> AND table3.gueltigAb <= '27/9/2005' AND ...
> AND table3.historisierung = (... )
> AND NOT EXISTS (select id FROM Table3 tableAlias2 WHERE ... )
>
> |--Compute Scalar(DEFINE:(& #91;Expr1015]=Conver
t([Expr1024])))
> |--Stream Aggregate(DEFINE:(& #91;Expr1024]=Count(
*)))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([table2].[table3]))
> |--Nested Loops(Inner Join, OUTER
> REFERENCES:([table1].[table2]))
> | |--Nested Loops(Left Anti Semi Join, OUTER
> REFERENCES:([table1].[id]))
> | | |--Sort(ORDER BY:([table1].[id] ASC))
> ...
> ==>> Very bad performance!
>
> Change NOT EXISTS not be last where statement of tables:
>
> SELECT count(*) FROM Table1 table1
> INNER JOIN Table2 table2 ON (table1.table2 = table2.id)
> INNER JOIN Table3 table3 ON (table2.table3 = table3.id)
> WHERE table3.oid = '3EAAF811-C8CB-11D3-9479-00600845FDA2'
> AND table1.gueltigAb <= '27/9/2005' AND ...
> AND NOT EXISTS (select id FROM Table1 tableAlias2 WHERE ... )
> AND table1.historisierung = (... )
> AND table2.gueltigAb <= '27/9/2005' AND ...
> AND NOT EXISTS (select id FROM Table2 tableAlias2 WHERE ... )
> AND table2.historisierung = (... )
> AND table3.gueltigAb <= '27/9/2005' AND ...
> AND NOT EXISTS (select id FROM Table3 tableAlias2 WHERE ... )
> AND table3.historisierung = (... )
>
> |--Compute Scalar(DEFINE:(& #91;Expr1015]=Conver
t([Expr1041])))
> |--Stream Aggregate(DEFINE:(& #91;Expr1041]=Count(
*)))
> |--Hash Match(Inner Join, HASH:([table3].[id],
> [table3].& #91;historisierung])
=([ST].[id], [Expr1009]),
> RESIDUAL:([ST].[id]=[table3].[id] AND
> [table3].& #91;historisierung]=
[Expr1009]))
> |--Nested Loops(Left Anti Semi Join, OUTER
> REFERENCES:([table3].[id]))
> | |--Clustered Index
> Scan(OBJECT:([VE2000].[VE2000].[Table3].[PK_Table3_23] AS [table3]),
> WHERE:(([table3].[gueltigAb]<='Sep 27 2005 12:00AM' AND
> [table3].[gueltigBis]>='Sep 27 2005 12:00AM') AND
> [table3].[ve]='3EAAF811-C8CB-11D3-9479-00600845FDA2'))
> | |--Row Count Spool
> | |--Top(1)
> | |--Clustered Index
> Seek(OBJECT:([VE2000].[VE2000].[Table3].[PK_Table3_23] AS [tableAlias2]),
> SEEK:([tableAlias2].[id]=[table3].[id]),
> WHERE:(Convert([tableAlias2].[geloescht])=1) ORDERED FORWARD)
> |--Merge Join(Inner Join,
> MERGE:([ST].[id])=([table2].[table3]),
> RESIDUAL:([table2].[table3]=[ST].[id]))
> ...
> ==>> Performance is ok!
>
> Instead of reordering WHERE clauses, using INNER HASH JOIN also leads to
> better performance.
>

Patrick

2005-09-30, 7:23 am

FULLSCAN solved the problem.

Thank you for the help,
Patrick

"Gert-Jan Strik" wrote:

> As the very first step: make sure the statistics are up to date. When in
> doubt, run UPDATE STATISTICS, preferably with FULLSCAN.
>
> Hope this helps,
> Gert-Jan
>

Sponsored Links