Delete & Insert getting slower & slower
-
To get data from a local 3rd party DB without many Primary Keys but lots of Foreign Keys on Server_A to a copy of the 3rd party DB on a remote server, I have this setup:
Server_A replicates the 3rd party database to a staging copy on Server_B "DB_B_1" (without FK constraints to allow replication).
Then "DB_B_1" on Server_B is copied to live version of 3rd party DB "DB_B_2" on Server_B using Deletes & Inserts, in the correct sequence to meet the FK constraints.
The Delete & Insert step has slowly been increasing in the length of time (5 min to 20 min), although the amount of data has only increased slightly. I ran DBCC DBREINDEX on all the affected DBs. What else should I look at to get speed back where it was ?
-
Dunno... could be anything... can you post the INSERT and DELETE code?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Not much to it. There're another 100 or so tables with this same format.:
DELETE
from TI_CMS.dbo.language_type
DELETE
from TI_CMS.dbo.metadata_type
DELETE
from TI_CMS.dbo.usergroups
DELETE
from TI_CMS.dbo.xml_collection_tbl
DELETE
from TI_CMS.dbo.task_type_tbl
DELETE
from TI_CMS.dbo.users
DELETE
from TI_CMS.dbo.content_folder_tbl
insert
into TI_CMS.dbo.content_folder_tbl select * from TI_CMS_Stage.dbo.content_folder_tbl
insert
into TI_CMS.dbo.users select * from TI_CMS_Stage.dbo.users
insert
into TI_CMS.dbo.xml_collection_tbl select * from TI_CMS_Stage.dbo.xml_collection_tbl
insert
into TI_CMS.dbo.metadata_type select * from TI_CMS_Stage.dbo.metadata_type
insert
into TI_CMS.dbo.task_type_tbl select * from TI_CMS_Stage.dbo.task_type_tbl
insert
into TI_CMS.dbo.usergroups select * from TI_CMS_Stage.dbo.usergroups
insert
into TI_CMS.dbo.language_type select * from TI_CMS_Stage.dbo.language_type
-
Ah... I see...
Part of the problem is that both DELETE's and INSERTS are logged so you seem to have a lot of unnecessary logging. The use of TRUNCATE would be better on the deletes but I believe that truncate won't work in the presence of foreign keys even if you truncate in the correct order (or I might be getting confused with #*$#&$$! Oracle
).You have all these wonder staging tables that contain all the necessary info and are in the correct format... instead of deleting and inserting all of that information, why not just do a drop of the old tables and rename the staging tables as new and reapply keys, indexes, and constraints? Sure, would take a little coding "magic" on your part but then would be scalable and fast.
Another way would be to make some surrogate views that point to two sets of tables... you can leisurly fill the temp tables with all the necessary keys, etc, and then just alter views to point at them. Reverse the process next time around. Would be a lightning quick change...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Since you're deleting everything and "starting over", you might care to:
- make sure your recovery model is on "simple" on the DB being rebuilt from scratch. That's a LOT of stuff to log, and you already have the data elsewhere. I missed a few of these when having to do similar things, and this can be a huge drain.
- you might care to drop all indexes first, then rebuild them when you're done. It's sometimes faster to do just that (longer T-SQL, but faster performance)
- Speaking of logged activities - try changing all of your DELETE statements to TRUNCATE TABLE statements (well - assuming they don't have a WHERE clause, as in your example).
You may find that one of the tables is causing the bulk of the problem (for example it's grown too big, and is now forcing some things to rely on disk cache instead of RAM, etc...) in which case you might need to "put a governor" on it and do multiple smaller inserts on that particular table (insert all a-f's in one shot, then g-m, etc...).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? -
"... The use of TRUNCATE would be better on the deletes but I believe that truncate won't work in the presence of foreign keys ..." Sad but true.
I'm already in simple mode.
"...drop all indexes first, then rebuild them ..." The DB getting inserted into is a 3rd party DB, so I've been hesitant to mess with it too much. If they change tables or indexes in a new release, I don't want to have anything hardcoded that might cause problems.
"... You may find that one of the tables is causing the bulk of the problem ..." Good thought
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply