Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

The feature to implement is to allow a user to select items and apply data update in bulk. It is very similar to JIRA's ability to bulk update a list of selected issues. In my case:

  • I iterate over the items,
  • and for each item, I simply call the existing method used for individually updating an item.

Take note, I cannot do this in sql level (ie. sql batch update) as business logic is in java.

Evidently, for performance, it will be slower given that a user decides to update multiple items, hence you can expect speed = # of items * rate of transaction. So in effect, updating an item individually should be same speed as bulk updating a list where the list contains a single item. That is fine, as its a trade-off.

My other concern however is this: the method used to update individually, will remain unchanged, and will simply be called by bulk update multiple times according to the number of selected items; this method acquires locks to involved tables until it finishes its transaction. This in effect, will cause locking for long time if there where multiple items to be updated hence a user will not be able to use the view and search page that retrieves from the locked tables. It's a trade-off that may not be as acceptable as the first point I discussed above.

I am conflicted, given that the nature of the existing method for individually updating an item, is really to acquire locks, and I am not suppose to touch that code. I am suppose to only reuse that method, calling it multiple times to implement the bulk update. Is this 2nd trade-off something we cannot bypass, is it a valid trade-off?

Please note also, I am not an expert in transactions and locks.

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

You certainly can use SQL and bulk updates is one of those areas in which it can be a superior solution (in terms of performance) than an ORM layer or firing of multiple small updates (with an overriding Transaction).

It's the cost of lots of networks calls vs a larger update statement.

You need to measure the performance difference, but I'm guessing that your I/O across the network is the larger bottleneck.

In terms of locks/txns etc. I'd carefully draw out where the boundaries of these are. You might find you're able to reduce the Txn overhead. Using AOP style Txns helps here, i.e. Injecting the start and end of a txn at exactly the points you need them and no more.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.