x

Updating a Table - What is best way to add/update, keeping performance in mind?

Hello, I was hoping someone could answer a few questions of mine regarding table updates as I'm an SQL noob.

I have a table on a linked server (Oracle) that I'm trying to duplicate in SQL Server... Initially I had simply created a procedure like this:

DROP TABLE xxx

SELECT *
INTO DBO.DETAIL
FROM XXX  //Using OpenQuery as is improved performance a lot.

But it's taking quite a long time to run... so I'm looking at other ways of doing this. I ran across the UPDATE command and had tried:

UPDATE DBO.DETAIL
SET field = B.field, etc...
FROM DBO.DETAIL A, xxx B
Where A.Key = B.Key

This command had updated every single line as well, which is alright I guess, but it seems to be unnecessary (I just need changes to be updated, not everything). In addition, it won't "add". For example, if table B has a new record, I'd like that to be added to table A - I imagine there is a simple add/update command, could anyone shed some light on the best way of doing this?

If you're also able to tell me whether or not the records will be locked during the update, that'd be appreciated so I know if I have to schedule this at night or if I can do this periodically throughout the day for the most up-to-date data.

Any comments/suggestions are very much appreciated. Thanks!

Sheep
more ▼

asked Nov 17, 2011 at 06:59 AM in Default

TelepathicSheep2 gravatar image

TelepathicSheep2
140 14 15 17

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Lots of options.

You could use SSIS to gather the data and import it.

I wouldnt drop the table, I'd simply truncate it. OK the Insert stament needs to change a litte and its only preference.

How many rows are invovled?

In your update have a comparison on the columns that are changed so that you only update rows that need it.

IE

UPDATE DBO.DETAIL
SET field = B.field, etc...
FROM DBO.DETAIL A, xxx B
Where A.Key = B.Key
and A.ColOne <> B.ColOne -- where ColOne is the column that has changed in Oracle and you want it updated in SQL

If the SQL Table is big then you may want to consider dropping and recreating indexes.

If the issue invovled new rows too the you should also investigate the MERGE command in SQL 2008 as it handles both updates and inserts in one command
more ▼

answered Nov 17, 2011 at 07:16 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

Thanks Fatherjack,

This table currently has about 150,000 rows, it will grow over time at about 10,000 rows/year or so I imagine. A lot of the data (~140,000 rows will not need to be updated as they are now historical). (There are about 60 columns)

I'll try the MERGE, and look into the SSIS, I'll also take a peek at indexes but feel that this might be over my head, thanks for your help.
Nov 17, 2011 at 07:25 AM TelepathicSheep2

If the source table is 'growing' then you should be able to leverage an ID column to check if it doesnt exist in the destination - for the new rows at least... something like

UPDATE DBO.DETAIL
SET field = B.field, etc...
FROM DBO.DETAIL A, xxx B
Where A.Key = B.Key
AND A.IDCol > (Select max(IDCol) from B)
Nov 17, 2011 at 07:38 AM Fatherjack ♦♦
(comments are locked)
10|1200 characters needed characters left

What you are describing is also known as an upsert (an update and insert in one).

You didn't mention which version of SQL Server you are using, so here are two possible ways to do what you are wanting:

/* Upsert method - works on SQL 2000 and above */
UPDATE  trg
SET     Col1 = src.Col1
FROM    dbo.Detail trg
INNER JOIN XXX src ON src.[Key] = trg.[Key]

INSERT  INTO dbo.Detail
        ([Key],
         Col1)
        SELECT  src.[Key],
                src.Col1
        FROM    XXX src
        LEFT JOIN dbo.Detail trg ON trg.[Key] = src.[Key]
        WHERE   trg.[Key] IS NULL

This does the UPDATE, and straight away a separate INSERT to add the missing data. AS you may realise, this is two separate operations and not really that great performance wise.

The next way is to use the MERGE command (SQL 2008 and above)

MERGE dbo.Detail trg
    USING 
        (SELECT [Key],
                Col1
         FROM   XXX) src
    ON (src.[Key] = trg.[Key])
    WHEN MATCHED AND
 (trg.Col1 <> src.Col1)
        THEN UPDATE
           SET      trg.Col1 = src.Col1
    WHEN NOT MATCHED 
        THEN INSERT ([Key], Col1)
           VALUES   (src.[Key],
                     src.Col1)
The MERGE command is optimised to fulfill this exact request
more ▼

answered Nov 17, 2011 at 07:48 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

got sidetracked before I could post, but I was done with it, so here it is anyway.
Nov 17, 2011 at 07:49 AM WilliamD
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x133
x67

asked: Nov 17, 2011 at 06:59 AM

Seen: 1650 times

Last Updated: Nov 17, 2011 at 07:12 AM