MIME-Version: 1.0
References: <CAKna9VY2v0XsDberzbJXZ4MqEW1RUtD0L_Mis_vrgEQWZgH0gg@mail.gmail.com>
 <4178E73A-24F5-4E3C-92F6-1532D8102C3E@kleczek.org> <CAKna9Vbt1VJu7Oa8FTWasgby+-kJn7omOhbfmWzkdpVwBiqNzQ@mail.gmail.com>
 <20240921143629.t2x37xfczeeunpnf@hjp.at> <CAKna9VZA5x1g7-4j8cQrW1ByqX5jtPwY7sY2MHuRgrxrOu4LBg@mail.gmail.com>
 <20240922192321.kpbe3p4ixmoid3ma@hjp.at>
In-Reply-To: <20240922192321.kpbe3p4ixmoid3ma@hjp.at>
From: Lok P <loknath.73@gmail.com>
Date: Sat, 5 Oct 2024 01:35:38 +0530
Message-ID: <CAKna9Vb39v+83mrsCV02W0yCKusW033rcc6bAOGKZj6qZBfsxQ@mail.gmail.com>
Subject: Re: How batch processing works
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000097f6960623ac322b"
Archived-At: <https://www.postgresql.org/message-id/CAKna9Vb39v%2B83mrsCV02W0yCKusW033rcc6bAOGKZj6qZBfsxQ%40mail.gmail.com>
Precedence: bulk

--00000000000097f6960623ac322b
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Sep 23, 2024 at 12:53=E2=80=AFAM Peter J. Holzer <hjp-pgsql@hjp.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=3D?,   column2=3D?, column3=3D? where
<PK_Column>=3D? ;


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

--00000000000097f6960623ac322b
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Mon, Sep 23, 2024 at 12:53=E2=80=
=AFAM Peter J. Holzer &lt;<a href=3D"mailto:hjp-pgsql@hjp.at">hjp-pgsql@hjp=
.at</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"marg=
in:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1e=
x"><br>
&gt; Thank you so much.<br>
&gt; I was expecting method-3(batch insert) to be the fastest or atleast as=
 you said<br>
&gt; perform with similar speed=C2=A0as method-2 (row by row insert with ba=
tch commit)<br>
<br>
Oops, sorry! I wrote that the wrong way around. Method 3 is the fastest.<br=
>
I guess I meant to write &quot;method2 takes about twice as long as method3=
&quot;<br>
or something like that.<br><br></blockquote><div><br></div><div>As in case =
of batch insert below=C2=A0is the fastest one as it inserts multiple=C2=A0r=
ows in one statement. Similarly I understand, Delete=C2=A0can be batched as=
 below. However, can you suggest how an Update can be batched in a simple/g=
eneric fashion in JDBC for an input data stream with multiple input values.=
 As because=C2=A0for an update if we write as below , it will just do one r=
ow update at a time?</div><div><br></div><div>Update &lt;table_name&gt; SET=
 column1=3D?, =C2=A0 column2=3D?, column3=3D? where &lt;PK_Column&gt;=3D? ;=
<br></div><div><br></div><div><br></div>INSERT INTO &lt;table_name&gt; VALU=
ES =C2=A0(1, &#39;a&#39;), (2, &#39;a&#39;),(3,&#39;a&#39;);</div><div clas=
s=3D"gmail_quote">Delete from &lt;table_name&gt; where column_name=C2=A0 in=
 (&lt;value1&gt;, &lt;value2&gt;,&lt;value3&gt;...);<br></div><div class=3D=
"gmail_quote"><br></div><div class=3D"gmail_quote">=C2=A0<br></div></div>

--00000000000097f6960623ac322b--