Best method for Delete Procedures
-
Running Win 2k Sp4, SQL 2000 SP3a
We have two databases (PROD and STAGING) with about 40 tables in each. The record count varies from 100's to 10mil+
I need to create a stored procedure for each table that deletes records from the PROD database where they don't exist in the STAGING database. I can't delete all the records in teh PROD database.
What's the best methodology for performing this delete?
DELETE FROM PROD.table1
FROM PROD.table1
LEFT JOIN STAGING.table1
ON PROD.table1.key = STAGING.table1.key
WHERE STAGING.table1.key IS NULL
or
DELETE FROM PROD.table1
FROM PROD.table1
WHERE PROD.table1.key NOT IN (
SELECT STAGING.table1.key
FROM STAGING.table1
)
or
something else ...
--------------------
Colt 45 - the original point and click interface
-
Hi!
There can be two ways of doing it:
1) truncate table on the production server and then insert all the records from STAGING server to PROD server.
2) the second option that you only have given.
The first option would be the faster one.
Best Regards,Indu Jakhar
-
Thanks for the response, but as I mentioned in my initial post, I can't delete all the records in the Production database.
--------------------
Colt 45 - the original point and click interface -
I take it you have some performance problems?
If your staging and production database are on the same box then I would use one of the methods you mention.
If the two databases are on separate boxes then I would have a routine to copy the production table onto the staging box explicitly to work out the records to delete.
As this is done on the staging box it only affects the users of the production system when the copying takes place.
The "records to delete" can be moved back to the production database at low traffic times and linked to the production table using an INNER JOIN.
It sounds a bit long winded but once the routine is set up it is quite quick.
We have a similar issue using the Stellent CMS but in that case the publishing process on the staging database is the most intensive process on the system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply