Slow execution of queries in a transaction | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Slow execution of queries in a transaction

I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout.
I have checked sp_lock myprocessid and I’ve noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks.
I even tried to set the isolation level to Read UNCOMMITED and still have the same problem.
As I said, once I execute my queries without being in a transaction everithing works great.
Can you help me to find out the problem? Thanks,
Laura

Try to see excution plan of each query and analize if any aditional indexes are neccesary.
Luis Martin
Moderator
SQL-Server-Performance.com Although nature commences with reason and ends in experience it is necessary for us to do the opposite, that is to commence with experience and from this to proceed to investigate the reason.
Leonardo Da Vinci Nunca esperes el reconocimiento de tus hijos, eso ocurrirá luego de tu muerte
All postings are provided “AS IS” with no warranties for accuracy.
Also run profiler to monitor the server while your transaction is running. This might highlight a particular step of the process that is suffering because of the transaction.
Have you checked any contention on TEMPDB and transaction log during the transaction operations. Ideally it will be somewhere in between, but generally low. If it#%92s pegged at 100% then you#%92re probably running a query that#%92s got all the data it needs in memory, and SQL Server is being forced to do something rather inefficient with it, like stepping through all the rows adding the value of some field for example. This can be a symptom of throwing RAM at the problem without optimizing, and will stop being a solution once the data outgrows memory again. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
Also, check if there is a trigger on destination table. I doubt read uncomitted will have an effect on insert … select … from queries and other data set based changes. Using transaction you just keep locks on all objects involved in such operation untill transaction is completed.
I am not sure where do you exactly execute the queries after transaction. But first, to have an idea of how "slow", run the SQL processes in the Query Analyzer and set this as your baseline runtime (and while you are in Query Analyzer, you might as well check if you have a good execution plan/query performance). You can also set the commandtimeout connection property to the appropriate value to prevent timeout in VB. If the overall performance (VB/SQL) is still unacceptable after the baseline, I suggest you move the SQL transactions and queries (using stored procedures) to the SQL Server (if that is possible) using BEGIN TRANSACTION/COMMIT instead of using the transaction to VB. Of course, there are design/maintenance issues that you also need to consider. But that is the realm of performance/space/complexity <img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />. <br /><br />Hope this helps.<br />
TRANSACTIONS should not span 10 minutes in duration. I think that is where the problem lies. With the transaction off, your queries that do updates lets say, release their locks immediately after the update is complete. Thus, any other user who ends up wanting access to that row can get it with no problem. And if they want to update something they can do their update, and their lock is released immediately after it completes. But with a TRANSACTION that spans all of that activity the lock for your updates/inserts/deletes are not removed until the entire thing completes. That means that someone else would have to wait for you to complete, and if they have done something that is locking rows you needed for a select or something you would just sit and wait and timeout because they also were in a TRANSACTION. My suggestion would be to figure what in the world in your system is so slow that the queries needed for a single transaction would take 10 minutes, and correct those issues. Otherwise you and your users are in for a terrible experience. Hope this helps,
Dalton
]]>