The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

One big update or several small ones in one transaction scope?

I have an update screen that displays all components that make a given product. Each component has id, price and supplier fields. When the user clicks update, I need to write the component info to database. My dilemma is this: Should I generate one big parameterized update statement and execute it or execute an update for each component seperately but participating in a single transaction.

Command command=New Command();
for(each Component component in Product.Components)
{
  BuildSqlCommand(command,component);
}
 
command.ExecuteNonQuery();

OR

for(each Component component in Product.Components)
{
  component.Update();
}
Urumi Send private email
Tuesday, February 27, 2007
 
 
Transaction.
TheDavid
Tuesday, February 27, 2007
 
 
>> execute an update for each component seperately but participating in a single transaction. <<

How many components?  Less than 20?  What is the chance of any one of the individual updates failing?

Recall that transactions get stored (typically) inside the logfile of your database, so the bigger the transaction, the more demand you place on it.  Also recall that if any part of a transaction fails, the whole thing gets rolled back.  The bigger the transaction, the longer the rollback time, and the more work you have to do to find out which of the individual updates was bad.
xampl Send private email
Tuesday, February 27, 2007
 
 
Agreed.

I should also point out that in the context of this application - components for a given part, it's very typical for each component to have its own business rules. For example, part A may only be sold in certain quantities. Part B may only be sold if it's actually in the warehouse. Part C may require you to fill in an additional form. And so on. It's much easier to deal with these additional requirements as separate items within a transaction.
TheDavid
Tuesday, February 27, 2007
 
 
I'd usually think of a transaction myself but if these individual elements are quite distinct I might find it safe to do individual updates so as not to burden the user with "SORRY LOST EVERYTHING YOU DID - TRY AGAIN!"

However, if these changes are closely related and that one without another means breaking referential integrity for example, transaction is the only way.
The Harbour Master Send private email
Tuesday, February 27, 2007
 
 
@Xampl:
less than 30 components. And the chance of the transaction failing is very low.

@David:
Each of these components have different business rules. But they are validated before the update command fires.

@The Harbour Master
The user prefers that all of it goes through or none does.

Again, Why not a single update statement? Thanks for the responses.
Urumi Send private email
Tuesday, February 27, 2007
 
 
I think both will effectively be the same.

If anything, the transaction may take a little longer than a single update, though not long enough to be worth worrying about it.
Senthilnathan N.S. Send private email
Tuesday, February 27, 2007
 
 
Hang on, if these fields are all on the same table - there's nothing to debate.

Are they all on the same table?
The Harbour Master Send private email
Tuesday, February 27, 2007
 
 
@The Harbour Master:
Yes, they are in the same table!?
Urumi Send private email
Tuesday, February 27, 2007
 
 
If the user prefers all or nothing, just do a transaction.  If it is one table, do a big update or several small updates.  Doesn't really matter, unless you're expecting to do a very large number of updates in a small period of time.

See UnitOfWork pattern.
Dan Fleet Send private email
Tuesday, February 27, 2007
 
 
There is a limit to the number of parameters that can be included in a batch so this might contrain a big update.

The only other issue would be the number of users and the extent to which you'd be generating traffic with a larger number of small commands as opposed to a single batch.
igs
Wednesday, February 28, 2007
 
 
I guess what I really meant was if they're on the same row in a table (there was much anti-normalisation sentiment on a parallel thread) then there's no good DB reason to update these things separately.
The Harbour Master Send private email
Wednesday, February 28, 2007
 
 
But re-reading 2 seconds after I sent my last response I can see that's not the case.

Personally, I would always opt for the transaction. Gives me less to think about.
The Harbour Master Send private email
Wednesday, February 28, 2007
 
 
It seems to me that the code would be cleaner if it's done as several different statements.  The performance will almost certainly be worse (more round-trips to the sql server), but probably still good enough.
bmm6o Send private email
Wednesday, February 28, 2007
 
 
"Each of these components have different business rules. But they are validated before the update command fires."

Urm...  not a good idea.  Race conditions.

In a multi-user system, it's theoretically possible for you to validate a package, then have another user update/insert his package and invalidate yours before you perform the actual update.

So you would normally do as a transaction - check to see if a part is available, and if so, sell it to a specific customer. If the part isn't available, report it as an error.

Keep in mind there's nothing wrong with doing this same validation in the application level. You'll probably want to do this anyway so that you can prepare an invoice before the customer pays for the items. But if one of the business rules is that you literally cannot sell stuff you don't have (and subsequently order replacements from the factory), then you need to make sure no one steals the last part at the last minute.
TheDavid
Wednesday, February 28, 2007
 
 
Since the user wants all-or-nothing, then you use a transaction.  That's as close to a no-brainer as you're going to get in a system like this.

Up above, Xampl noted that the bigger the transaction, the longer the rollback time.  I would contend that if the time to roll failed transactions back is an issue that you need to give significant consideration to, then you probably have a bigger problem:  why are enough transactions failing that you care how long it takes to roll them back? 

I would also take a long look at your process and see how much of it can be accomplished in a stored procedure.  If your application tier is creating a transaction, formulating and executing UPDATE statements, and then committing the transaction, you're incurring an awful lot of overhead.  Since your transaction is occurring on multiple machines, you're using the distributed transaction coordinator to manage it.  Since your SQL is being formulated dynamically, the database server is building an execution plan for every statement every time you execute it.  Every statement incurs the latency cost of a round-trip to the database server.

Contrast this with a stored procedure that takes an XML document containing the updates as its parameter.  There's one round trip to the database server, the server doesn't have to build an execution plan, and the transaction takes place entirely within the database.

You can do large numbers of updates extremely efficiently using this pattern.  Using it is contingent on knowing how to use XML effectively and write efficient stored procedures, but if you don't it's well worth learning.
Robert Rossney Send private email
Wednesday, February 28, 2007
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz