Hi
I have a nightly refresh job that pushes changes to data out to the production DB from a staging DB. I can only connect from the staging DB to the production DB. There fore all my inserts,updates and deletes are being executed locally on the staging server. Some of the tables have 1m plus rows. Therefore to delete rows from production or update rows It needs to pull the 1m row locally to execute the query and then send the updates or deletes back out to the remote server (sometimes of 2 or 3 rows might have changed). I have about 45 tables and this refresh sp is killing the productionDB when it is run.
Is there anyway to speed this up. Can I push the updates to remote server to update them there. I originally used a DateLastUpdated field on the tables and compared them to identify changes. Now I use an indicator (ActionCode) to identify the rows that have changed. This speeded up inserts but made little difference to updates and deletes. here are the scripts I used for
The inserts arent so bad
Insert INTO ProductionDB.Catalog.DBO.Table
SELECT fields....
From StagingDB..Table AS STAGING
where STAGING.ActionCode ='I'
But the updates and deletes are very slow
DELETE ProductionDB.Catalog.DBO.Table
FROM ProductionDB.Catalog.DBO.Table Prod
INNER JOIN StagingDB..Table staging ON ...
WHERE Staging.ActionCode = 'D'
UPDATE ProductionDB.Catalog.DBO.Table
SET FIELD = STAGING.FIELD
....
FROM ProductionDB.Catalog.DBO.Table Prod
INNER JOIN StagingDB..Table staging ON ...
WHERE Staging.ActionCode = 'U'
Cheers
A