Обсуждение: 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.
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
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 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.
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.
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.
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. > > > > > > > > > > > >
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
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
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