May 15, 2009 at 7:08 am
Dear all,
I have long pondered why a data modification performed within a transaction consistently takes longer than if not in a transaction.
I think i have come across this quite often in various DML scenarios, but the example that really catches my eye is when i do one or more INSERT ... SELECT statements into a temp table and then finally INSERT ... SELECT from the temp table results into a permanent table. I do this sort of thing while building flattened reporting tables overnight.
The final INSERT ... SELECT may take 1 minute let's say when not in a transaction. It may take 4 mins when within a transaction which included the INSERT ... SELECT statements into the temp table.
Why is this?! Has anyone found the same thing?
I understand that there are increased locking resources required when keeping the transaction open - might this be the reason? I seem to recall that some analysis i did in the past made me question this, as other processes were not affected by the locking overhead. Just my final INSERT ... SELECT was. If it was general resource contention due to all the extra locks held, surely this would not be the case. So why does my own specific connection suffer?
e.g.
--BEGIN TRAN
CREATE TABLE #TMP_ONE(
...
,...
)
INSERT INTO #TMP_ONE
SELECT
col_list
FROM
perm1
join
perm2 on ...
etc
etc
INSERT INTO Target_Reporting_Table
SELECT col_list
FROM#TMP_ONE
This final INSERT SELECT will take much longer if the BEGIN TRAN is run at the start than if no transaction is used (wrapping a transaction around the final INSERT SELECT only, i.e. not around the inial INSERT .. SELECT into the temp table as well, does not impact the run time much if at all).
Is a read from a temp (or permanent) table which has been locked within the same process take longer than a read from a non-locked table?
(By the way I am working in SS 2000 SP4 and haven't tried to reproduce this behavior in 2005.)
It has just got me curious, that's all. Any insight here would be appreciated!
Many thanks,
James M
June 15, 2009 at 1:29 pm
Hopefully, this address your question, but you should also read up on what transactions are/do.
When you put the TRANS at the top SQL has to do a lot of extra work to save state. If you do a rollback, you are going to roll EVERYTHING back, including the CREATE TABLE statement. So, all that information has to be stored in the log for a potential rollback. Also, depending on your ISOLATION LEVEL, SQL might be holding locks on the tables in your query. Additionally, it has to COMMIT all that information at the end. So, you are making SQL do a lot of extra/unneeded work be placing the TRANS at the beginning (assuming you don't need to rollback creating and loading the temp table).
Given your sample, I do not see a need for a transaction at all. The final insert is atomic (a transaction unto itself) so it’ll rollback if it fails. Now, if you need to do inserts into several tables (or updates or deletes) , then you would, most likely, want to wrap those in a transaction.
Cheers!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply