Re: Performance Question Stored Procedure

• Previous message: Deepson Thomas: "Re: all about transaction"
• In reply to: Paul Say: "Re: Performance Question Stored Procedure"
• Messages sorted by: [ date ] [ thread ]

Date: Tue, 14 Dec 2004 10:21:19 +0100

On Tue, 14 Dec 2004 14:13:00 +1000, Paul Say wrote:

>I am not sure exactly what you mean, do you have a simple example.

Hi Paul,

Assuming that your tables are like this:

CREATE TABLE TableA (KeyCol int NOT NULL PRIMARY KEY,
                     OtherCol char(7) NOT NULL,
                     LastCol datetime)
CREATE TABLE TableB (KeyCol int NOT NULL PRIMARY KEY,
                     OtherCol char(7) NOT NULL,
                     LastCol datetime)

and that you have to update TableA with rows from TableB, you should use:

-- First, update rows already in TableA
UPDATE a
SET a.OtherCol = b.OtherCol,
            a.LastCol = b.LastCol
FROM TableB AS b
INNER JOIN TableA AS a
      ON a.KeyCol = b.KeyCol
-- Optionally:
WHERE a.OtherCol <> b.OtherCol
AND ( NULLIF (a.LastCol, b.LastCol) IS NOT NULL
      OR NULLIF (b.LastCol, a.LastCol) IS NOT NULL)

-- Next, insert rows that were not already in TableA
INSERT INTO TableA (KeyCol, OtherCol, LastCol)
SELECT b.KeyCol, b.OtherCol, b.LastCol
FROM TableB AS b
LEFT JOIN TableA AS a
     ON a.KeyCol = b.KeyCol
WHERE a.KeyCol IS NULL

NOTES:
* Both queries are untested, so they might have typos
* The UPDATE syntax I used is proprietary T-SQL format. To do the same in
ANSI-compliant SQL, you need a longer statement with the same subquery
repeated several times; unfortunately, these are not optimized well by SQL
Server. Only use this UPDATE syntax if you are positive that each row in
the updated table matches exactly one row in the other table(s) used in
the query, unless you like undefined behaviour.
* The INSERT statement could also be rewritten to use a NOT EXISTS clause.
The choice between NOT EXISTS or LEFT JOIN is purely by personal
preference.

Best, Hugo

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

• Previous message: Deepson Thomas: "Re: all about transaction"
• In reply to: Paul Say: "Re: Performance Question Stored Procedure"
• Messages sorted by: [ date ] [ thread ]