November 25, 2009 at 12:24 pm
As seems to happen on a daily basis, I'm trying to puzzle out a performance issue on a process we have.
We have an Agent job that calls an SSIS package that calls a SP.
The SP starts a transaction, then truncates data from some tables, does some bulk inserts into these tables, then does updates on a few tables to clean up the data.
In working on trying to speed up the process, the developer reordered how some things are done in this process.
Originally, the truncate occurs right before the table inserts. It looks something like this:
BEGIN TRAN
TRUNCATE TABLE A
BULK INSERT INTO TABLE A
TRUNCATE TABLE B
BULK INSERT INTO TABLE B
TRUNCATE TABLE C
BULK INSERT INTO TABLE C
UPDATE TABLE A
UPDATE TABLE B
COMMIT TRAN
This ran in about 4 hours.
The second crack at it is identical in every way, except the order.
BEGIN TRAN
TRUNCATE TABLE A
TRUNCATE TABLE B
TRUNCATE TABLE C
BULK INSERT INTO TABLE A
BULK INSERT INTO TABLE B
BULK INSERT INTO TABLE C
UPDATE TABLE A
UPDATE TABLE B
COMMIT TRAN
This runs in 8 hours.
Any ideas on why?
November 25, 2009 at 1:17 pm
That is strange. The truncates should only take a second or two to drop the extents. The BULK INSERTS should let go of the tables and run normally. Can you run timings in between each statement in both orders and see what might be taking longer?
Add an
insert into log table select 'Truncate A', getdate()
truncate TableA
insert into log table select 'Truncate B', getdate()
truncate TableB
etc.
November 25, 2009 at 1:45 pm
We were capturing that already. It would have been helpful to mention it in my first post, eh? 🙂
The only non-negligible differences between the two runs comes in the updates. The inserts are all done within plus or minus a second or two. The updates go from 11 minutes on the fast run, to 2 hours on the slow one.
Some other random details.
We're not doing anything with the indexes during the process. We're leaving them in place, rather than dropping and removing them.
We're doing WITH TABLOCK on the bulk inserts, and the DB is running minimally logged.
The entire process is running in a transaction.
The reason the dev moved all the truncates to the beginning was because he wanted the transaction to go ahead and lock those tables ASAP, so any users querying the data mid-process wouldn't be able to see it until the entire transaction was done. That worked, but of course it has doubled the amount of time it takes the process to run.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply