SQL Operation Performance Advice

[TimHop12]

Hello,

Here is the my situation: I have a table "products" of about 10,000 records in System 1. How I have to update this table intermittently, from a different System 2, which contains exactly same table and more correct data.

Approach 1: Delete all the records from table "products" in System 1 and load it from System 2 afresh.

Approach 2: Check against all the records existing in System2 for every record we have in System1; if exists update it or insert it.

Please advice.

Thanks

[Delerna]

There maybe another alternative.
If there is a date of entry field in the table. ie, the date the record is added to the table.
You could save the max(date) each time you transfer records.
that way you could just select all records greater than that date and transfer just those. Then you save the max(date) again.

or

you could do the same thing if there is a sequential ID field.


If not, I would opt for Delete contents and transfer all records. 10,000 records is really not that many records. If you did it with DTS then it would be pretty much set and forget, except for checking for failures of course. Even that can be arranged so that an email gets sent to someone in case of failure

[Delerna]

i just noticed

if exists update it or insert it.

I would go for a complete delete and transfer running with DTS afterhours.
The development overheads of the second option isn't worth trying reduce the amount of time it would take to transfer 10,000 records
But that just my opinion.
Having said that, lots of little savings can add up to 1 big saving, so it also depends on your situation.

Actually, were assuming the second option will be quicker, which im not entireley convinced it would be.

[ck9663]

Hello,

Here is the my situation: I have a table "products" of about 10,000 records in System 1. How I have to update this table intermittently, from a different System 2, which contains exactly same table and more correct data.

Approach 1: Delete all the records from table "products" in System 1 and load it from System 2 afresh.

Approach 2: Check against all the records existing in System2 for every record we have in System1; if exists update it or insert it.

Please advice.

Thanks

Approach 1: Watch out for relationship among the tables. If you use delete and you have an IDENTITY column, you might need to reset it.

Approach 2: Will take more processing time.Watch out for table relationship when updating.You might want to check if everything is updated.

Approach 3: Replication. One word: COMPLICATED ;)

-- CK