slow read in a transaction

[ram_kri]

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

 

[W.G. Ryan eMVP]

If possible, I'd cut as much client code out as possible. You can create a
job for instance that creates a file within the context of a transaction.
So a job or DTS package may work. In a similar vein is doing a fire and
forget type scenario running things asynch a viable option for you, or is a
'real' performance increase the only viable option you have?