Hello,
I need help in converting a correlated query to an uncorrelated query
to optimize the performance.
The query looks something like this:
SELECT DISTINCT gwcompanyguid FROM ADDRESS0 as adresse WHERE
(SELECT count(Document0.updatetimestamp) FROM Document0 WHERE
( gguid IN (SELECT guid2 FROM tablerelation WHERE guid1
=adresse.gguid)
AND(Document0.updatetimestamp > cast('01.05.2006' as datetime) ))) = 0
Advice would be really great!
TIA,
Patrick
Hello, Patrick
Here are some other ways to write your query:
SELECT DISTINCT gwcompanyguid FROM ADDRESS0 as adresse
WHERE NOT EXISTS (
SELECT * FROM Document0 WHERE gguid IN (
SELECT guid2 FROM tablerelation WHERE guid1=adresse.gguid
) AND Document0.updatetimestamp > cast('01.05.2006' as datetime)
)
SELECT DISTINCT gwcompanyguid FROM ADDRESS0 as adresse
WHERE NOT EXISTS (
SELECT * FROM Document0
INNER JOIN tablerelation ON Document0.gguid=tablerelation.guid2
WHERE tablerelation.guid1=adresse.gguid
AND Document0.updatetimestamp > cast('01.05.2006' as datetime)
)
SELECT DISTINCT gwcompanyguid FROM ADDRESS0 as adresse LEFT JOIN
Document0
INNER JOIN tablerelation ON Document0.gguid=tablerelation.guid2
ON tablerelation.guid1=adresse.gguid
AND Document0.updatetimestamp > cast('01.05.2006' as datetime)
WHERE Document0.gguid IS NULL
Test all of them to see which has the best performance (and to ensure
you get the expected results).
Razvan
-----------------------------------------------Reply-----------------------------------------------
Thank you very much Razvan for your examples.
The original query is however a bit more complicated. I just shortened
it here to simplify the question.
I now tested the first 2 examples in the original query.
The first didn't came back on SQL Server 2000 but on 2005 as the
original query didn't came back on 2000.
The second example of yours was an improvement, cause the query
returned a result in a reasonable timespan.
Now I try to get to work your third example....but I was not able to
convert it to the more complicated query. Here is what I came up with:
SELECT DISTINCT adresse.gwcompanyguid FROM ADDRESS0 as adresse
LEFT JOIN
Document0 as doc1
INNER JOIN tablerelation as tr1 ON doc1.gguid=tr1.guid2
ON tr1.guid1=adresse.gguid
AND doc1.updatetimestamp > cast('01.05.2006' as datetime)
AND tr1.tablesign2 = 'DOC'
LEFT JOIN
Document0 as doc2
INNER JOIN tablerelation as tr2 ON doc2.gguid=tr2.guid1
ON tr2.guid2=adresse.gguid
AND doc2.updatetimestamp > cast('01.05.2006' as datetime)
AND tr2.tablesign2 = 'DOC'
WHERE doc1.gguid IS NULL AND doc2.gguid IS NULL
You see I want to query both sides of tablerelation. This query
however doesn't return the right results.
Could you or someone else help me with this.
TIA,
Patrick
-----------------------------------------------Reply-----------------------------------------------
My fault, the third example works as well. I just made the error at
the second "AND tr2.tablesign2 = 'DOC' ". It has to be "AND
tr2.tablesign1 = 'DOC' "
So it works.
Thank you again! :-)
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
