November 19, 2008 at 7:54 am
Good day Forum.
In the app I'm working on I have seen the following delete statement:
DELETE from tblFOO where fooKey IN
(
Select F.fooKey from tblFoo F inner join tblBar B
ON F.abc = B.abc
inner join tblBaz B2 ON B.abc = B2.abc
inner join tblGamma G ON B2.abc = G.abc
WHERE (list of conditions)
)
I thought this would be less optimal than the following:
DELETE F FROM
tblFoo F inner join tblBar B ON F.abc = B.abc
inner join tblBaz B2 ON B.abc = B2.abc
inner join tblGamma G ON B2.abc = G.abc
WHERE (list of conditions)
So I plugged both into query analyzer and the first one evaluates to 40 %, the second to 60%.
Why would this be?
Thanks
November 20, 2008 at 2:58 am
The second query may produce a lot of duplicate rows in tblFOO.
Also, joins in subqueries always seem to be done with nested loops which is rarely efficient.
(Derived tables are fine.)
If you want to do a ANSI delete you should try nested EXISTS statements.
eg.
DELETE tblFOO
WHERE fooKey IN
(
    SELECT F.fooKey
    FROM tblFoo F
    WHERE EXISTS
    (
        SELECT *
        FROM tblBar B
        WHERE F.abc = B.abc
            AND EXISTS
            (
                SELECT *
                FROM tblBaz B2
                WHERE B.abc = B2.abc
                    AND EXISTS
                    (
                        SELECT *
                        FROM tblGamma G
                        WHERE B2.abc = G.abc
                            AND (G where conditions)
                    )
                    AND (B2 where conditions)
            )
            AND (B where conditions)
    )
    AND (F where conditions)
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply