batch update in SQL

[swapnil kataria]

In java when we need to do multiple rows update in a table,
we use batch functionality

I want to do this in data base in a store procedure,

anyone have some idea how to do this in sql or postgresql

please help, thanks in advance

[Akhilesh Trivedi]

swapnil kataria wrote:
anyone have some idea how to do this in sql or postgresql

PL/SQL or PostgreSQL?

[Avi Abrami]

Swapnil,
Perhaps I am misunderstanding your question, but an SQL UPDATE statement can update any number of rows in a table, for example:
update TABLE set COLUMN = VALUE
This will update all the rows in the table.

Good Luck,
Avi.

[swapnil kataria]

hi akhilesh,

you can tell me in postgresql, if you know otherwise sql is also fine

[Akhilesh Trivedi]

I suppose Swapnil may beusing 'update' as a generalized term, like an INSERT is also a kind of update in the table.

[swapnil kataria]

hi Bear Bibeault ,

I know this is not a JAVA question, but I thought any of rancher must know this thing,
So I put this.

[swapnil kataria]

hi avi and alkesh

suppose I have to update following rows of a table, I want to do it in a single batch, because I have to do hundred of records

update table_name set value = 'x' where id=1;
update table_name set value = 'xx' where id=2;
update table_ name set value = 'xxx' where id=3;
update table_ name set value = 'xxxx' where id=4;
update table_name set value = 'xxxxx' where id=5;
update table_name set value = 'xxxxxx' where id=6;

i hope you got my problem

[Akhilesh Trivedi]

what stops you from using, something like "...where id>=1 AND id <=6"

[swapnil kataria]

AKHILESH

It is not necessary to be between 1 and 6,
it could be any id and i need to hundred of different records
and value which I have to set that is also different,
hope you got

[Akhilesh Trivedi]

swapnil kataria wrote:...hope you got

No I did not, but did you get difference between PL/SQL and PostgreSQL?

[Gamini Sirisena]

what is the source of your data?

For example is it in a csv file with two columns that are the value and the id?
Or do you have to query another database table to get these values and ids?
Or some other way?

[swapnil kataria]

GAMINI,

I have written a STORE PROC having a loop,

so I have to update records in loop, but it will update one by one in loop, which is not efficient,
so I want to do in a batch,

[Gamini Sirisena]

so if I assume you are querying some tables to get the required data would something like the below help?


update public.target b set value=a.somevalue from
(select somevalue, id from public.source) a where b.id=a.id;

edit:
this will work with postgresql, not sure about other databases

[Martin Vashko]

In Oracle's PL/SQL there are the BULK functionality for this kind of processing, it is the equivalent of array binding. Later versions of Oracle (probably form 11g on) provide this functionality automatically behind the scenes.

Other databases might have similar constructs or optimizations, the best you could do is to consult your database documentation.

[Avi Abrami]

Swapnil,
As I understand your problem, I think JDBC batch update is what you are looking for.
I suggest you do an Internet search for the terms: "JDBC" "batch" "update"
The results of my search are here.

Good Luck,
Avi.

[swapnil kataria]

HI GAMINI

please read this, hope you would get my requirement

update table_name set value = 'x' where id=1;
update table_name set value = 'xx' where id=2;
update table_ name set value = 'xxx' where id=3;
update table_ name set value = 'xxxx' where id=4;
update table_name set value = 'xxxxx' where id=5;
update table_name set value = 'xxxxxx' where id=6;

I have to do hundred of records , so I want to do it in a batch

[Martin Vashko]

The crucial question is: where do the data you're using in your stored procedure come from?

They may come from another table which already exists in the database, then Gamini's suggestion can be used (updating a key-preserved view might actually be better in some databases), assuming your DB supports it. Most DB's do, some perform it better than others.

Or your data come from your Java program. In this case, you could create a "staging" table in the database (be it a temporary table or a normal one) and use Java SQL batching to efficiently load data into it, then use Gamini's suggestion..... you see.

Or they come from another table with some data-crunching applied. Chances are you could express the data-crunching in pure SQL and do it all in one SQL command. In Oracle, this could probably be tuned to perform well (better than any other solution). In other databases - I don't know.

Only if what you need cannot be expressed in SQL in your database and must be computed by procedural code, you'd had to use procedural code and in this case might use features of your database to speed things up. As I've already mentioned, in Oracle there is a family of BULK operations (BULK COLLECT, BULK INSERT, BULK UPDATE) which serves this purpose. Other databases might have something similar; as I've already mentioned, your best bet is to read the docs, or specify the exact version of your database here and hope someone will be able to help you.

After you answer the crucial question here and post details about your database (vendor, version...), we can elaborate on the solution that suits you best.

[swapnil kataria]

hi Martin Vajsar

My DB is postgresql, and in a STORE_PROC , i have to do updates in a loop for a table,
suppose that loop do 100 updates one by one, I want to do it in a single batch as in java( addbatch, executebatch),

I have read somewhere I can put autocommit off before loop and commit after loop,

set autocommit off;
for loop
update query
end
commit;

so whole record will be update after loop, which is efficient, but could not able to implement that

[Martin Vashko]

Hi,

swapnil kataria wrote:My DB is postgresql,

I won't be able to help you in detail with postgresql, I don't know it. I can only offer general advice.

and in a STORE_PROC , i have to do updates in a loop for a table

No. This describes how are you going to do it, not what do you need to do or where do the data come from.

You need to show us how you obtain the data you need to for the updates. Where do they come from? Are the data hard-coded in the stored proc? (Probably not, but even this could be done differently.) Do they come from another query? Are they somehow processed after they've been read from that query? Etc etc.

[query]suppose that loop do 100 updates one by one, I want to do it in a single batch as in java( addbatch, executebatch)[/query]
The trick in speeding things up with JDBC batching lies in two factors (at least for Oracle):

1) JDBC batching reduces number of network roundtrips. This obviously speeds things up in all databases. However, this optimization equally obviously cannot be done for stored procs, as they already run on the sever and don't send data over the network at all.

2) Specifically for Oracle, the JDBC batching (with Oracle 11 version driver) uses additional boost, in which the INSERT or UPDATE is executed once for all parameters that were set in the batch. This functionality can be used in PL/SQL with the BULK keyword I've already mentioned several times (and promise I won't mention it again ). Something like this might be available in PostgreSQL, or not. Quick peek into PostgreSQL's PL/SQL reference (they have one, I hope) might help you determine whether or not something similar is supported there.

BUT, if your data are not hardcoded in your PL/SQL stored proc, chances are the whole update can be performed by one update statement. That will always be faster than any procedural code. Always. Stop thinking about implementing JDBC batching in PL/SQL and determine, or let us help you determine, whether you can use the really optimal way.

I have read somewhere I can put autocommit off before loop and commit after loop.

Autocommit is a terrible disaster. I'd be surprised if PostgreSQL even had such a feature in PL/SQL (Oracle certainly does not have it). You should always have it off. Unless you're using the database as some kind of an inefficient, transaction-less file system and don't care about how transactions occur, that is.

Moreover, frequent commits make some databases perform better, but in other it makes things worse. In PostgreSQL it probably makes things worse (as it does in Oracle and DB2), as they use multiversioning for concurrency control. In SQL Server, MySQL etc. it may help, as they rely on locks for concurrency, which are then hold for shorter periods. Regardless of this, you should always make the transaction as long as it logically needs be - no longer, no shorter.

Cheers,
Martin

[swapnil kataria]

thanks Martin
I got it now