Transaction is slowing down the select queries
Hi Folks,
I have a situation here..
There is a transaction that I start from within my C# code on a Connection object and hit the DB to execute a Stored Procedure. The SP has some insert statements, some update statements and then some SELECT statements also. Now after I read the data back I need to process the data I got back (due to the SELECT query inside the SP) and generate a file. Then after I generate the file I commit the transaction.
The data is as huge as 100,000 records that get returned. Now because the SELECT query is inside a transaction that its taking lot of time to give the data back to C# code, I guess since the transaction is not committed yet that there is locking stuff happening inside my SQL Server 2000 and making the data reading using the DataReader slow.
If I isolate the inserts and updates into a separate transaction in the SP (and do the Selects after I commit the insert-update-SP)then what if my file generation fails ? Say I solve it by executing another SP that reverts the data to its original state when my file-gen process throws exception. But what if the System Shutdown happens after the Sp is executed and before the file generation happens ? The file is not generated but the inserts and updates are committed. I dont want to do any manual updation of DB to take it back to its original state, I want it to happen automatically but still I should be able to get rid of the bottleneck of slow reading of data (which takes approximately 20 minutes).
Any suggestions on this would be real helpful.
Thanks in advance.
Cheers,
Kris
