Running code as a transaction takes 3 times longer

  • Thread starter Thread starter cyndire
  • Start date Start date
C

cyndire

I have some code in C# to delete a table with many children tables, so
I am doing a cascade delete. I put it into a transaction (using
TransactionOption.Required)so if anything blows everything is rolled
back. The problem is, it takes like 20 seconds to execute. If I
disable the transaction, the code runs in about 7 seconds. Why does
this happen, and is there any way I can speed up the transaction?
 
It happens because the entire table is being stored to make sure it can be
recovered in case of rollback. Depending on what your database is, you can
move the transaction to the database to improve perf (SQL Server sproc for
example).

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

************************************************
Think Outside the Box!
************************************************
 
Back
Top