Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Help needed for converting a correlated query to an uncorrelated query


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