Re: update 2 fields problem

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 01/12/05 

Date: Wed, 12 Jan 2005 09:05:22 +0100

On Wed, 12 Jan 2005 09:55:59 +0800, Agnes wrote:

>Thanks Juraj, however, some said JOIN will make SQL statment process
>slowley, will it make my program getting slow ?

Hi Agnes,

Whoever told you that just shows a thorough lack of understanding of SQL
Server.

Sure, joining can be slow if you don't have appropriate indexes - but the
same can be said about subqueries or straight one-table queries: without
proper indexes, ALL queries will be slow.

The choice between using a join or using a subquery is often only a matter
of personal taste, as the SQL Server optimizer will usually generate the
same execution plan for both queries.
This changes if the subquery has to be duplicated. The optimizer doesn't
seem to understand that it's not necessary to repeat the execution of two
completely equal subqueries. As soon as you find yourself repeating the
same subquery expression, you should consider converting to a join.

There are, however, other problems with the proprietary UPDATE FROM syntax
suggested by Juraj. For starters, it is non-standard, so you won't be able
to port your code to any other RDBMS. But even more important is that the
result is not well defined in some cases. Here's a simple example, using
the UPDATE FROM syntax:

 UPDATE OneTable
 SET Column1 = OtherTable.Column1
            Column2 = OtherTable.Column2
 FROM OneTable
 INNER JOIN OtherTable
       ON OtherTable.KeyCol = OneTable.KeyCol
 WHERE OneTable.Selection = 'Y'

Two important things to note are:
1. If a row in OneTable is not matched to any row in OtherTable, it won't
be affected by the UPDATE statement. In most cases, this is the desired
result - but not in all cases!
2. If a row in OneTable is matched to more than one row in OtherTable, the
new values for Column1 and Column2 will be based on the values in any of
the matching rows. Which of the matching rows will be used can't be
predicted. (SQL Server will use the last matching row if finds, but the
order in which matches are found depends on the execution plan, and the
execution plan may change when new indexes are created, a service pack is
applied, available memory changes or SQL Server feels like it).

The corresponding UPDATE statement in ANSI-standard syntax would look like
this:

 UPDATE OneTable
 SET Column1 = (SELECT Column1
                       FROM OtherTable
                       WHERE OtherTable.KeyCol = OneTable.KeyCol)
            Column2 = (SELECT Column2
                       FROM OtherTable
                       WHERE OtherTable.KeyCol = OneTable.KeyCol)
 WHERE OneTable.Selection = 'Y'
 -- The following is optional - see comments below
 AND EXISTS (SELECT *
             FROM OtherTable
             WHERE OtherTable.KeyCol = OneTable.KeyCol)

Two important things to note are:
1. If a row in OneTable is not matched to any row in OtherTable, it will
still be affected by the UPDATE statement and the new values for Column1
and Column2 will be NULL. If this is not desired, then the subquery has to
be repeated in the WHERE clause (as indicated above) to exclude this
column from the set to be updated.
2. If a row in OneTable is matched to more than one row in OtherTable, the
subquery will result in more than one value; SQL Server will raise an
error condition to signal that you need to change the subquery so as to
result in no more than one value. The error message might be a pain, but
it's much better than trying to find the cause of an error because a
change in execution plan causes SQL Server to no longer pick the row that
it always picked before!

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Whats the deal with PAGEIOLATCH_SH?
    ... Does SQL Server have trouble when a single table is this size? ... About 20 minutes into the query everything goes bad. ... >> go over some threshold, which seems to vary, the disk queue length ... >> I'll work on getting the real execution plan, ...
    (microsoft.public.sqlserver.programming)
  • Re: SP WITH RECOMPILE Option
    ... It sounds as though some user is logging in and specifying parameter inputs ... that cause SQL Server to choose an execution plan which is suboptimal for ... SQL Server MVP ... > the time-out error but on the SQL server it cause a COMPILE x lock on the ...
    (microsoft.public.sqlserver.server)
  • Re: Insert via another tables columns
    ... The UPDATE with a SET (SELECT subquery) also requires that col_a1 and col_a2 ... UPDATE clause, beside the subquery in the SET clause. ... Earlier version of FoxPro need to use xbase REPLACE command or a mix of SQL ... I try the same in SQL Server and works great. ...
    (microsoft.public.fox.programmer.exchange)
  • Re: SPs executes slower using SP3
    ... SQL Server does not have a seperate cache for each CPU, ... but it may have more than one copy of the execution plan if the plan is set ... > I viewed the execution plans from my workstation vs. my co-workers ...
    (microsoft.public.sqlserver.clients)
  • Incorrect behavior in NOT IN subquery with OPENXML
    ... In SQL Server 200 SP4 I'm trying to use an OPENXML statement in a subquery. ... I've tried to simplify this problem and I've ended up with the sql script ...
    (microsoft.public.sqlserver.xml)
Loading