Re: How batch processing works

On 10/4/24 1:05 PM, Lok P wrote:
>
>
> On Mon, Sep 23, 2024 at 12:53 AM Peter J. Holzer <hjp-pgsql(at)hjp(dot)at
> <mailto:hjp-pgsql(at)hjp(dot)at>> wrote:
>
>
> > Thank you so much.
> > I was expecting method-3(batch insert) to be the fastest or
> atleast as you said
> > perform with similar speed as method-2 (row by row insert with
> batch commit)
>
> Oops, sorry! I wrote that the wrong way around. Method 3 is the fastest.
> I guess I meant to write "method2 takes about twice as long as method3"
> or something like that.
>
>
> As in case of batch insert below is the fastest one as it inserts
> multiple rows in one statement. Similarly I understand, Delete can be
> batched as below. However, can you suggest how an Update can be batched
> in a simple/generic fashion in JDBC for an input data stream with
> multiple input values. As because for an update if we write as below ,
> it will just do one row update at a time?
>
> Update <table_name> SET column1=?,   column2=?, column3=? where
> <PK_Column>=? ;

UPDATE table_name SET column1 = vals.text_val, column2=int_val FROM
(VALUES (1, 'dog', 23),(2, 'cat', 44)) AS vals (id, text_val, int_val)
where table_name.id = vals.id;

>
>
> INSERT INTO <table_name> VALUES  (1, 'a'), (2, 'a'),(3,'a');
> Delete from <table_name> where column_name  in (<value1>,
> <value2>,<value3>...);
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com