September 4, 2008 at 10:25 am
I am having what appears to be a replication problem. What I am currently doing is checking for changes and when I find them deleting the row and inserting the changed one. This can be up to 1.5 million updates and about 75,000 inserts per day.
Replication is then sending out both the deletes and inserts and I am seeing a large lag time and of course locking and blocking while the deletes are applied.
This is an extremely wide table with approx 150 columns and I don't have any control over that. There are also about 20 million rows to contend with.
Would it be better to just do an update or are we on the right track by doing the deletes and inserts?
September 4, 2008 at 12:23 pm
Update is a Delete and Insert.
Your only bet is to change a design.
September 4, 2008 at 1:09 pm
Glen (9/4/2008)
Update is a Delete and Insert.Your only bet is to change a design.
We assumed the same thing and knew that SQL treated an Update as a Delete then Insert, but after much more research and more testing we found that there is a cost of up to 4 times with doing an Delete and Insert over just doing the Update.
We have modified one of our main SSIS packages that manipulates the most number of rows and will let it run tonight to see if this also reduces the impact on replication as we suspect that it will greatly.
As for changing the design... As stated in the original post we have no control over the database schema. This is a practice management system was created by a third party and can not be changed. We are moving off of this system over the next 1 - 2 years but we have to maintain this data for at least the next 3 - 4 years while AR moves off and patients are migrated to the new system.
Jeff
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply