SQL Server Performance Forum – Threads Archive

Performance issue in Transaction Processing

Hi, We are inserting 1000 rows in a table, when we do this outside the transaction, that is, without transactino processing, it is able to complete in a minute. Where as, when we do the same insertion inside a transaction processing, it takes 4 minutes, what could be the reasons, what are all the things we need to checkup, your help will be highly helpful, thank you. Regards,
Deva

is there a concurrency in your transaction processing?

What isolation level is used for the transaction? Are source tables tables (tables you select from for insert statement) frequently updated?

hmm…can you post the transaction that you wrote so that I can get a better understanding on how the transaction is being processed? Thanks <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

some thing is strange, inside a tx, there are fewer log writes, so there must be some thing else

Hi Marovic, Q: What isolation level is used for the transaction? Are source tables tables (tables you select from for insert statement) frequently updated? A: We are using the sql server’s default isolation level, that is
Read Committed. Source tables and data storage table is not updated while the processing takes place. This problem is very critical for us, your help would be useful for us,
thank you. Satya: Please throw us some light on this, thank you. Regards,
Deva

Considering how litle time I have right now and all respect I have for Joe, I think it would be smart to try to involve him more. Right now, I think it would be useful to know how did you test insert with transaction, from VB or from query analyzer. Is the insert statetment part of the stored procedure?

Hi Marovic, Thank you, I tested from query analyzer, yes, insert statement is part of the stored procedure. Basically, here we do the business process from a stored procedure, in turn, it inserts data into the output table, when we do this insertion inside a transaction processing (begin tran … commit tran), it is taking more time visibly. We tried removing transaction processing and carried out the same process, it is going very fast. Please tell us, what sql server does internally during transaction processing, how data is mananged during tranaction processing, I also try to refer sql server internals book to get more knowledge on that, thanks again. Regards,
Deva

sqldev, sorry, I was away for a while. The main difference AFAIK is that the whole transaction is not marked completed until it is committed or rolled back, while without transactions each statetment is separate transaction. It is the same with locking. Locks on first statement inside transaction are not released until the whole transaction is completed. That’s the reason I have tried to find a locking problem. Does your transaction contains other statements then insert you mentioned?
Is there a trigger on destination table? Even 1 minute for 1000 rows is too much, so it must be something wrong here.

Also, is udf is part of select statetment used for insert?

Hi, Thank you all, the problem I identified here is, when we use bulk insert/update inside the transaction processing, it is taking more time than it is suppose to, I mean when I carry out the same operation without transaction processing (begin tran … commit tran), it is much faster. Here, I used a bulk update statement, which updates aggregate data through correlated sub query, that was really taking a huge time, I changed that query to use derived table instead of correlated sub query, after that, it gets over in a reasonable time, thank you. Regards,
Deva

Dear Deva I am facing a problem some what similar to yours. Kindly clarify the bulk insert that you mentioned is applied in the following way or in a different manner Begin Tran Insert IntoSourceTable (Field1,Field2….Field2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Select(Field1,Field2….Field2<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> From#TempTable Commit Tran

Hi, Yes exactly, I am talking about the "Insert into.. Select" bulk statement and "Update" statement which updates multiple rows. When we keep this inside transaction processing, it visibly takes more time than the one which does not use transaction processing, we manage this problem by writing the DML statements in the optimized way which we could and it is going on, thank you. Regards,
Deva

]]>