Drop Table

Support Forum for database administrators and web based access to important newsgroups related to databases
Register on Database Support Forum Edit your profileCalendarFind other Database Support forum membersFrequently Asked QuestionsSearch this forum -> 
For Database admins: Free Database-related Magazines Now Free shipping to Texas


Post New Thread










Thread
Author
NOT EXISTS bad performance
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 WHER
E
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].[hist
orisierung]=[Expr1009]))
|--Nested Loops(Left Anti Semi Join, OUTER
REFERENCES:([table3].[id]))
|    |--Clustered Index
Scan(OBJECT:([VE2000].[VE2000].[Table3].[PK_Table3_23] AS &#
91;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 &#
91;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.

Report this thread to moderator Post Follow-up to this message
Old Post
Patrick
09-29-05 02:23 PM

Re: NOT EXISTS bad performance
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 WH
ERE
> 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 ver
y
> bad performance. If NOT EXISTS is not the last where clause, it uses a HAS
H
> or MERGE JOIN, and it is running fine like in SQLServer 7.0. I cant see an
y
> reason why it does this.
>
> Maybe it is related to
> http://support.microsoft.com/?scid=...15593&x=6&y=12, so it cou
ld
> be a bug? But I tested with SP4 and SP3 and get the same (bad) results. Wi
th
> SP2 it also changes the executation plan when I reorder the statements, bu
t
> 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].	
1;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].[hi
storisierung]=[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:([tabl
e2].[table3]=[ST].[id]))
> ...
> ==>> Performance is ok!
>
> Instead of reordering WHERE clauses, using INNER HASH JOIN also leads to
> better performance.

Report this thread to moderator Post Follow-up to this message
Old Post
Gert-Jan Strik
09-30-05 01:23 AM

Re: NOT EXISTS bad performance
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.
>

Report this thread to moderator Post Follow-up to this message
Old Post
Jerry Spivey
09-30-05 08:23 AM

Re: NOT EXISTS bad performance
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
>

Report this thread to moderator Post Follow-up to this message
Old Post
Patrick
09-30-05 12:23 PM

Sponsored Links



Last Thread Next Thread
Post New Thread

MS SQL Server archive

Show a Printable Version Email This Page to Someone! Receive updates to this thread
Microsoft SQL Server
Access database support
PostgreSQL Replication
SQL Server ODBC
FoxPro Support
PostgreSQL pgAdmin
SQL Server Clustering
MySQL ODBC
Web Applications with dBASE
SQL Server CE
MySQL++
Sybase Database Support
MS SQL Full Text Search
PostgreSQL Administration
SQL Anywhere support
DB2 UDB Database
Paradox Database Support
Filemaker Database
Berkley DB
SQL 2000/2000i database
ASE Database
Forum Jump:
All times are GMT. The time now is 01:11 PM.

 
Mobile devices forum | Database support forum archive



Copyrights DropTable.com Database Support Forum 2004 - 2006