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 everything works great.
Can you help me to find out the problem
Thanks,
Laura
Slow execution of queries inside transaction
RajeshPKumar
I forgot to tell you that even after I broke my transaction into few other transactions, I still have the problem. It is stopping to one of the "Insert Into . ..Select" statement. The Select is returning around 6000 records. In Query Analyzer this particular INSERT INTO statement takes few seconds. In my VB code is not finished even after 30 min.The previous statements before this one are inserting just few records, they are executed very fast, I don't get any problems with them.
I guess I better give up to any transactions and just write statements to delete the inserted data if something goes wrong.
Thanks
Philip Wagenaar - Nashuatec
Thank you Mike.
I appreciate your help.
John Chorlton
I would just about assuredly peg this as a hardware issue of some sort if you hadn't said that running the exact same code in QA runs immediately but from VB it doesnt.
If you have a hardware setup issue (large inserts where the log, data, indexes, tempdb are all located on the same drive for example will take more time, and if you have > 1 processor (possibly even hyperthreading) you can set a machine in a tizzy with some larger queries like that.) it would behave the same way regardless of the tool.
When faced with a query running really long, you need to open profiler and watch. Also perfmon (search for perfmon on www.sql-server-performance.com for more information about what to look for) and look for memory, disk, and/or cpu issues (memory can be the hardest to diagnose)
Then look at sp_who2 and sp_lockinfo (in 2000) or dm_exec_sessions and dm_exec_requests and dm_tran_locks views. Here are links to a few queries of mine:
Locks
http://spaces.msn.com/drsql/blog/cns!80677FB08B3162E4!906.entry
Processes
http://spaces.msn.com/drsql/blog/cns!80677FB08B3162E4!900.entry
Anything else is just a guess as to why you are having problems, and I would hate to see you give up and manually rollback transactions if you don't have to (can't guarantee that you can optimize what you have without spending money, but who knows.)
Sarada
Ian Tien
OK. If the 6000 record select/insert is the big killer, I'm going to guess that it's an issue with the transaction log. Manually doing a rollback through deletes looks to be a pretty big win for you here.
DucNgo
I have a lot of inserts into different tables. Most of my queries are something like:
Insert Into DB1..MyTable(....) Select From DB2..SomeTable Join DB2..OtherTable.....
The information I insert is identifying a simulation case in my software. In case something goes wrong, I want all the data I inserted to disapear from my database and that's why I used this large transaction.
But you got a point here. I could still break my transaction into few shorted transactions.
Thanks.
Trajecto
What kinds of queries are you running If you're doing a lot of inserts and updates, you could be experiencing slowdown from having a large transaction log. The inserts could also be creating locks as you insert rows.
Incidentally, what are you doing that you need to hold a transaction open for 30 minutes I try to design my applications so they do their inserts and updates in a single block, so my transactions are fairly short. If I'm doing a LOT of inserts, I try to break it into multiple transactions to keep the database happy.
Yurik
They are both located on the same server.