Thread: Multiple Row Insert vs. Batch
Say I need to insert 250 rows. Is single-statement multiple row insert or JDBC batch likely to be faster?
Thanks!
R.
batch should be faster and if it isn't we did something wrong
On 6 June 2015 at 12:34, Robert DiFalco <robert.difalco@gmail.com> wrote:
Say I need to insert 250 rows. Is single-statement multiple row insert or JDBC batch likely to be faster?Thanks!R.
I think OP meant something like
Insert into table values (?,?,?),(?,?,?),...,(?,?,?);
Vs batch of insert into table values(?,?,?);
I really think first would be faster up to a certain amount of rows, but test is needed to check.
Vitalii Tymchyshyn
Сб, 6 черв. 2015 13:30 Dave Cramer <pg@fastcrypt.com> пише:
batch should be faster and if it isn't we did something wrongOn 6 June 2015 at 12:34, Robert DiFalco <robert.difalco@gmail.com> wrote:Say I need to insert 250 rows. Is single-statement multiple row insert or JDBC batch likely to be faster?Thanks!R.
Robert ??
Is that what you meant ?
On 6 June 2015 at 13:34, Vitalii Tymchyshyn <vit@tym.im> wrote:
I think OP meant something like
Insert into table values (?,?,?),(?,?,?),...,(?,?,?);
Vs batch of insert into table values(?,?,?);I really think first would be faster up to a certain amount of rows, but test is needed to check.
Vitalii Tymchyshyn
Сб, 6 черв. 2015 13:30 Dave Cramer <pg@fastcrypt.com> пише:batch should be faster and if it isn't we did something wrongOn 6 June 2015 at 12:34, Robert DiFalco <robert.difalco@gmail.com> wrote:Say I need to insert 250 rows. Is single-statement multiple row insert or JDBC batch likely to be faster?Thanks!R.
Yes that's correct. A multiple row insert with a single insert statement. I'll try benchmarking the two on Monday. My guess is that they are pretty damn similar.
Sent from my iPhone
Sent from my iPhone
Robert ??Is that what you meant ?On 6 June 2015 at 13:34, Vitalii Tymchyshyn <vit@tym.im> wrote:I think OP meant something like
Insert into table values (?,?,?),(?,?,?),...,(?,?,?);
Vs batch of insert into table values(?,?,?);I really think first would be faster up to a certain amount of rows, but test is needed to check.
Vitalii Tymchyshyn
Сб, 6 черв. 2015 13:30 Dave Cramer <pg@fastcrypt.com> пише:batch should be faster and if it isn't we did something wrongOn 6 June 2015 at 12:34, Robert DiFalco <robert.difalco@gmail.com> wrote:Say I need to insert 250 rows. Is single-statement multiple row insert or JDBC batch likely to be faster?Thanks!R.
I did a benchmark and multi-row insert with a single statement is about 6-7% faster than batch insert. This is for 250 rows executed a thousand times in a loop (alternating between the two to reduce subsequent table insert index slowdown). So a little faster but not significantly so.
On Sat, Jun 6, 2015 at 12:07 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:
Yes that's correct. A multiple row insert with a single insert statement. I'll try benchmarking the two on Monday. My guess is that they are pretty damn similar.
Sent from my iPhoneRobert ??Is that what you meant ?On 6 June 2015 at 13:34, Vitalii Tymchyshyn <vit@tym.im> wrote:I think OP meant something like
Insert into table values (?,?,?),(?,?,?),...,(?,?,?);
Vs batch of insert into table values(?,?,?);I really think first would be faster up to a certain amount of rows, but test is needed to check.
Vitalii Tymchyshyn
Сб, 6 черв. 2015 13:30 Dave Cramer <pg@fastcrypt.com> пише:batch should be faster and if it isn't we did something wrongOn 6 June 2015 at 12:34, Robert DiFalco <robert.difalco@gmail.com> wrote:Say I need to insert 250 rows. Is single-statement multiple row insert or JDBC batch likely to be faster?Thanks!R.
Another interesting thing is that for 250 records COPY is actually the slowest. But these are real world tests to that could have been the time it took to transform the 250 records to CSV.
On Sun, Jun 7, 2015 at 9:41 AM, Robert DiFalco <robert.difalco@gmail.com> wrote:
I did a benchmark and multi-row insert with a single statement is about 6-7% faster than batch insert. This is for 250 rows executed a thousand times in a loop (alternating between the two to reduce subsequent table insert index slowdown). So a little faster but not significantly so.On Sat, Jun 6, 2015 at 12:07 PM, Robert DiFalco <robert.difalco@gmail.com> wrote:Yes that's correct. A multiple row insert with a single insert statement. I'll try benchmarking the two on Monday. My guess is that they are pretty damn similar.
Sent from my iPhoneRobert ??Is that what you meant ?On 6 June 2015 at 13:34, Vitalii Tymchyshyn <vit@tym.im> wrote:I think OP meant something like
Insert into table values (?,?,?),(?,?,?),...,(?,?,?);
Vs batch of insert into table values(?,?,?);I really think first would be faster up to a certain amount of rows, but test is needed to check.
Vitalii Tymchyshyn
Сб, 6 черв. 2015 13:30 Dave Cramer <pg@fastcrypt.com> пише:batch should be faster and if it isn't we did something wrongOn 6 June 2015 at 12:34, Robert DiFalco <robert.difalco@gmail.com> wrote:Say I need to insert 250 rows. Is single-statement multiple row insert or JDBC batch likely to be faster?Thanks!R.
El dom, 07-06-2015 a las 09:56 -0700, Robert DiFalco escribió: > Another interesting thing is that for 250 records COPY is actually the > slowest. But these are real world tests to that could have been the > time it took to transform the 250 records to CSV. Hi Robert, Copy using CopyManager( https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html ) should be fasterthan multi-row inserts and batch single-row insert. Did you count only the copy time without transformation time ? > > On Sun, Jun 7, 2015 at 9:41 AM, Robert DiFalco > <robert.difalco@gmail.com> wrote: > I did a benchmark and multi-row insert with a single statement > is about 6-7% faster than batch insert. This is for 250 rows > executed a thousand times in a loop (alternating between the > two to reduce subsequent table insert index slowdown). So a > little faster but not significantly so. > > On Sat, Jun 6, 2015 at 12:07 PM, Robert DiFalco > <robert.difalco@gmail.com> wrote: > Yes that's correct. A multiple row insert with a > single insert statement. I'll try benchmarking the two > on Monday. My guess is that they are pretty damn > similar. > > Sent from my iPhone > > On Jun 6, 2015, at 10:35 AM, Dave Cramer > <pg@fastcrypt.com> wrote: > > > > Robert ?? > > > > > > Is that what you meant ? > > > > Dave Cramer > > > > dave.cramer(at)credativ(dot)ca > > http://www.credativ.ca > > > > > > On 6 June 2015 at 13:34, Vitalii Tymchyshyn > > <vit@tym.im> wrote: > > I think OP meant something like > > Insert into table values > > (?,?,?),(?,?,?),...,(?,?,?); > > Vs batch of insert into table values(?,?,?); > > > > I really think first would be faster up to a > > certain amount of rows, but test is needed > > to check. > > > > Vitalii Tymchyshyn > > > > > > > > Сб, 6 черв. 2015 13:30 Dave Cramer > > <pg@fastcrypt.com> пише: > > > > batch should be faster and if it > > isn't we did something wrong > > > > Dave Cramer > > > > dave.cramer(at)credativ(dot)ca > > http://www.credativ.ca > > > > > > On 6 June 2015 at 12:34, Robert > > DiFalco <robert.difalco@gmail.com> > > wrote: > > Say I need to insert 250 > > rows. Is single-statement > > multiple row insert or JDBC > > batch likely to be faster? > > > > > > Thanks! > > > > > > R. > > > > > > > > > > > >
To make it apples to apples I included the time to transform the payload. It's maybe 1-4% faster for 250 rows with 3 fields sampled 100 times with a JVM warm-up.
On Mon, Jun 8, 2015 at 2:27 AM, jaime soler <jaime.soler@gmail.com> wrote:
El dom, 07-06-2015 a las 09:56 -0700, Robert DiFalco escribió:
> Another interesting thing is that for 250 records COPY is actually the
> slowest. But these are real world tests to that could have been the
> time it took to transform the 250 records to CSV.
Hi Robert,
Copy using
CopyManager( https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html ) should be faster than multi-row inserts and batch single-row insert. Did you count only the copy time without transformation time ?>
> On Sun, Jun 7, 2015 at 9:41 AM, Robert DiFalco
> <robert.difalco@gmail.com> wrote:
> I did a benchmark and multi-row insert with a single statement
> is about 6-7% faster than batch insert. This is for 250 rows
> executed a thousand times in a loop (alternating between the
> two to reduce subsequent table insert index slowdown). So a
> little faster but not significantly so.
>
> On Sat, Jun 6, 2015 at 12:07 PM, Robert DiFalco
> <robert.difalco@gmail.com> wrote:
> Yes that's correct. A multiple row insert with a
> single insert statement. I'll try benchmarking the two
> on Monday. My guess is that they are pretty damn
> similar.
>
> Sent from my iPhone
>
> On Jun 6, 2015, at 10:35 AM, Dave Cramer
> <pg@fastcrypt.com> wrote:
>
>
> > Robert ??
> >
> >
> > Is that what you meant ?
> >
> > Dave Cramer
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> > On 6 June 2015 at 13:34, Vitalii Tymchyshyn
> > <vit@tym.im> wrote:
> > I think OP meant something like
> > Insert into table values
> > (?,?,?),(?,?,?),...,(?,?,?);
> > Vs batch of insert into table values(?,?,?);
> >
> > I really think first would be faster up to a
> > certain amount of rows, but test is needed
> > to check.
> >
> > Vitalii Tymchyshyn
> >
> >
> >
> > Сб, 6 черв. 2015 13:30 Dave Cramer
> > <pg@fastcrypt.com> пише:
> >
> > batch should be faster and if it
> > isn't we did something wrong
> >
> > Dave Cramer
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> > On 6 June 2015 at 12:34, Robert
> > DiFalco <robert.difalco@gmail.com>
> > wrote:
> > Say I need to insert 250
> > rows. Is single-statement
> > multiple row insert or JDBC
> > batch likely to be faster?
> >
> >
> > Thanks!
> >
> >
> > R.
> >
> >
> >
> >
>
>
>
>
Hi Robert.
That sounds interesting. Is your test code published / publishable, for peer review? Are you using jmh or any similar tool for conducting the test? Have you considering throwing latency measures into the test (where hdrhistogram may be a great helper).
Sorry for asking too many questions, but I hope they help :)
Regards,
-- Álvaro Hernández Tortosa ----------- 8Kdata
On 08/06/15 12:29, Robert DiFalco wrote:
To make it apples to apples I included the time to transform the payload. It's maybe 1-4% faster for 250 rows with 3 fields sampled 100 times with a JVM warm-up.On Mon, Jun 8, 2015 at 2:27 AM, jaime soler <jaime.soler@gmail.com> wrote:El dom, 07-06-2015 a las 09:56 -0700, Robert DiFalco escribió:
> Another interesting thing is that for 250 records COPY is actually the
> slowest. But these are real world tests to that could have been the
> time it took to transform the 250 records to CSV.
Hi Robert,
Copy using
CopyManager( https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html ) should be faster than multi-row inserts and batch single-row insert. Did you count only the copy time without transformation time ?>
> On Sun, Jun 7, 2015 at 9:41 AM, Robert DiFalco
> <robert.difalco@gmail.com> wrote:
> I did a benchmark and multi-row insert with a single statement
> is about 6-7% faster than batch insert. This is for 250 rows
> executed a thousand times in a loop (alternating between the
> two to reduce subsequent table insert index slowdown). So a
> little faster but not significantly so.
>
> On Sat, Jun 6, 2015 at 12:07 PM, Robert DiFalco
> <robert.difalco@gmail.com> wrote:
> Yes that's correct. A multiple row insert with a
> single insert statement. I'll try benchmarking the two
> on Monday. My guess is that they are pretty damn
> similar.
>
> Sent from my iPhone
>
> On Jun 6, 2015, at 10:35 AM, Dave Cramer
> <pg@fastcrypt.com> wrote:
>
>
> > Robert ??
> >
> >
> > Is that what you meant ?
> >
> > Dave Cramer
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> > On 6 June 2015 at 13:34, Vitalii Tymchyshyn
> > <vit@tym.im> wrote:
> > I think OP meant something like
> > Insert into table values
> > (?,?,?),(?,?,?),...,(?,?,?);
> > Vs batch of insert into table values(?,?,?);
> >
> > I really think first would be faster up to a
> > certain amount of rows, but test is needed
> > to check.
> >
> > Vitalii Tymchyshyn
> >
> >
> >
> > Сб, 6 черв. 2015 13:30 Dave Cramer
> > <pg@fastcrypt.com> пише:
> >
> > batch should be faster and if it
> > isn't we did something wrong
> >
> > Dave Cramer
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> > On 6 June 2015 at 12:34, Robert
> > DiFalco <robert.difalco@gmail.com>
> > wrote:
> > Say I need to insert 250
> > rows. Is single-statement
> > multiple row insert or JDBC
> > batch likely to be faster?
> >
> >
> > Thanks!
> >
> >
> > R.
> >
> >
> >
> >
>
>
>
>
-- Álvaro Hernández Tortosa ----------- 8Kdata
Sorry, the code is not available, yes JMH. :( I may be able to do that at some point. One thing to note. I am using "SET LOCAL synchronous_commit TO OFF" on the session for these inserts. For the single statement/multi-row version I am able to have one round trip because I can send this command + ';' + the multi-row insert. For CopyManager I need to create a separate statement and execute it on its own (so an extra round-trip).
On Mon, Jun 8, 2015 at 10:14 AM, Álvaro Hernández Tortosa <aht@8kdata.com> wrote:
Hi Robert.
That sounds interesting. Is your test code published / publishable, for peer review? Are you using jmh or any similar tool for conducting the test? Have you considering throwing latency measures into the test (where hdrhistogram may be a great helper).
Sorry for asking too many questions, but I hope they help :)
Regards,
-- Álvaro Hernández Tortosa ----------- 8KdataOn 08/06/15 12:29, Robert DiFalco wrote:To make it apples to apples I included the time to transform the payload. It's maybe 1-4% faster for 250 rows with 3 fields sampled 100 times with a JVM warm-up.On Mon, Jun 8, 2015 at 2:27 AM, jaime soler <jaime.soler@gmail.com> wrote:El dom, 07-06-2015 a las 09:56 -0700, Robert DiFalco escribió:
> Another interesting thing is that for 250 records COPY is actually the
> slowest. But these are real world tests to that could have been the
> time it took to transform the 250 records to CSV.
Hi Robert,
Copy using
CopyManager( https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/copy/CopyManager.html ) should be faster than multi-row inserts and batch single-row insert. Did you count only the copy time without transformation time ?>
> On Sun, Jun 7, 2015 at 9:41 AM, Robert DiFalco
> <robert.difalco@gmail.com> wrote:
> I did a benchmark and multi-row insert with a single statement
> is about 6-7% faster than batch insert. This is for 250 rows
> executed a thousand times in a loop (alternating between the
> two to reduce subsequent table insert index slowdown). So a
> little faster but not significantly so.
>
> On Sat, Jun 6, 2015 at 12:07 PM, Robert DiFalco
> <robert.difalco@gmail.com> wrote:
> Yes that's correct. A multiple row insert with a
> single insert statement. I'll try benchmarking the two
> on Monday. My guess is that they are pretty damn
> similar.
>
> Sent from my iPhone
>
> On Jun 6, 2015, at 10:35 AM, Dave Cramer
> <pg@fastcrypt.com> wrote:
>
>
> > Robert ??
> >
> >
> > Is that what you meant ?
> >
> > Dave Cramer
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> > On 6 June 2015 at 13:34, Vitalii Tymchyshyn
> > <vit@tym.im> wrote:
> > I think OP meant something like
> > Insert into table values
> > (?,?,?),(?,?,?),...,(?,?,?);
> > Vs batch of insert into table values(?,?,?);
> >
> > I really think first would be faster up to a
> > certain amount of rows, but test is needed
> > to check.
> >
> > Vitalii Tymchyshyn
> >
> >
> >
> > Сб, 6 черв. 2015 13:30 Dave Cramer
> > <pg@fastcrypt.com> пише:
> >
> > batch should be faster and if it
> > isn't we did something wrong
> >
> > Dave Cramer
> >
> > dave.cramer(at)credativ(dot)ca
> > http://www.credativ.ca
> >
> >
> > On 6 June 2015 at 12:34, Robert
> > DiFalco <robert.difalco@gmail.com>
> > wrote:
> > Say I need to insert 250
> > rows. Is single-statement
> > multiple row insert or JDBC
> > batch likely to be faster?
> >
> >
> > Thanks!
> >
> >
> > R.
> >
> >
> >
> >
>
>
>
>-- Álvaro Hernández Tortosa ----------- 8Kdata