Re: Multiple Row Insert vs. Batch

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(at)8kdata(dot)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
>
>
> -----------
> 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(dot)soler(at)gmail(dot)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(dot)difalco(at)gmail(dot)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(dot)difalco(at)gmail(dot)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(at)fastcrypt(dot)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(at)tym(dot)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(at)fastcrypt(dot)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(dot)difalco(at)gmail(dot)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
>
>
>