Eitan said:
Hello,
Besides changing the timeout,
is there something else I can do ?
Thanks
Yes. Reduce the amount of work taking place in your transaction.
It is often the case, but not always, that a great deal of effort is placed
within the boundaries of a DB transaction, where some of it can easily be
off-loaded to another operation.
I can illustrate this much better with an example. Of course, this is
purely fictional, but I hope it is instructive...
In an order system, say that your transaction needs to iterate through all
of the records associated with a specific customer and recalculate the
discounts applied to every order. For a major customer, this could take a
LONG time. In addition, it MUST be done in a single pass because, in most
existing systems, you don't have additional flags that you can use to
indicate if the work was done or not.
However, if you look at it, the majority of the time is not spent acutally
updating the database. The majority of the time is spent recalculating the
discount values, especially if you are doing that in SQL.
So, to offload this effort, I would suggest two passes on the data.
Create a table for the operation (OrderDiscountUpdate). In that table, put
three columns: RecordId (PK, FK to Orders table), NewDiscount (money), and
DiscountUpdated (boolean).
Call a SQL Insert stmt to get the list of records to modify. Create the
records in your new table. Set the "NewDiscount" value equal to the existing
discoung value and set the DiscountUpdated flag to False.
Now, in your application, you can iterate through this table, recalculating
the discounts. Update the flag as you go. This work is entirely outside of
a transaction and in fact, is quite interruptable. It can be done in a
background thread. If you do take a while, make sure you capture any new
orders that were added while you were running.
Last step: start a transaction, and issue an update statement against the
original Orders table, using the data from the OrderDiscountUpdate table.
Commit your transaction. This step is short, quick, and efficient.
The concept here is that the hard work is usually not transactional work.
You want to protect the data in the update, but you don't always need to
include all the work in the transaction.
HTH,
--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik
Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
