Update table instantly or “Bulk” Update in database later? And is it advisable?

Question

Hi,

I have a question regarding a semi-constant update in a database. In short it is regarding a checkout function on a web page, which each time the checkout function is evoked it do five steps.

I want to try to optimize this function and have my eye on a step where I update a table each time the checkout is performed. I take the information retrieved from the shopping cart and then update the table in question.

I do have some indexes on the table, the gain from those are greater than leaving them so this is a cost I’m willing to take.

Now, my question is. Could it in some way regarding to performance be better to not update the table instantly but collect every checkout items and save them in some way (maybe in a file) and then at a specific time (or several times) at day take this file and then update the table with the new information.

Then I started thinking about if there was a possibility to use some sort of Bulk Update to take a file, hashmap, array (or?) and then update it.

And I’m using IBM DB2 version 9.7

Mestika

Answer 1

You will lose the ability to do transactions, or to recover from failure after a step midway, so I would avoid using this approach. You could try using prepared statements, or batch updates offered by JDBC 2.0 where multiple statements are submitted to the DB as a single unit.