Re: Optimizer question

From: Delbert Glass (delbert_at_noincoming.com)
Date: 03/19/04 

Date: Fri, 19 Mar 2004 10:55:19 -0600

(untested)

Eliminating the question:

  UPDATE ToBeChanged SET
    Column1 = 'Something'
  WHERE EXISTS (
    SELECT *
    FROM Table2
    WHERE EXISTS (
      SELECT *
      FROM Table3
      WHERE Table3.Column2 = Table2.Column3
      AND Table3.Column4 = ToBeChanged.Column5
    )
    AND NOT EXISTS (
      SELECT *
      FROM Table3
      WHERE Table3.Column2 = Table2.Column3
      AND Table3.Column4 = ToBeChanged.Column5
      AND Table3.Column6 IS NOT NULL
    )
  )

Being tricky might be faster:

  UPDATE ToBeChanged SET
    Column1 = 'Something'
  WHERE EXISTS (
    SELECT *
    FROM Table2
    WHERE EXISTS (
      SELECT COUNT(*)
      FROM Table3
      WHERE Table3.Column2 = Table2.Column3
      AND Table3.Column4 = ToBeChanged.Column5
      HAVING COUNT(*) = COUNT(Table3.Column6)
      AND COUNT(*) > 0
    )
  )

Twisting that around might be even faster:

  UPDATE ToBeChanged SET
    Column1 = 'Something'
  WHERE EXISTS (
    SELECT COUNT(*)
    FROM Table3
    WHERE Table3.Column4 = ToBeChanged.Column5
    AND EXISTS (
      SELECT *
      FROM Table2
      WHERE Table2.Column3 = Table3.Column2
    )
    HAVING COUNT(*) = COUNT(Table3.Column6)
    AND COUNT(*) > 0
  )

But as Karl mentioned, "It depends!"

(
Note, the first of the three is the easiest
for a reader to understand of the four.
)

Bye,
Delbert Glass

"Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message
news:luhl50tgstcl7suem8mbb44cutb4pu61hd@4ax.com...
> I'm using MS SQL Server 2000. Suppose I have a query like this:
>
> (1) UPDATE ToBeChanged
> (2) SET Column1 = 'Something'
> (3) WHERE EXISTS
> (4) (SELECT *
> (5) FROM Table2
> (6) INNER JOIN Table3
> (7) ON Table3.Column2 = Table2.Column3
> (8) AND Table3.Column4 = ToBeChanged.Column5
> (9) AND NOT EXISTS
> (10) (SELECT *
> (11) FROM Table3 AS T3Inner
> (12) WHERE T3Inner.Column2 = Table2.Column3
> (13) AND T3Inner.Column4 = ToBeChanged.Column5
> (14) AND T3Inner.Column6 IS NOT NULL))
>
> (Line numbers are added for easy referral; they're not part of the
> query)
>
> Because of the join condition in lines 7 and 8, I could change lines
> 12 and 13 to:
>
> (12b) WHERE T3Inner.Column2 = Table3.Column2
> (13b) AND T3Inner.Column4 = Table3.Column4
>
> and still get the same results. I can only make this change on only
> one of these lines.
>
> Is this an execution strategy that the query optimizer will consider
> or should I try both variations in situations similar to the one
> described here?
>
> (Note: Similar situations also often arise when joining more than two
> tables.)
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

Loading