Re: Multiple Row Insert vs. Batch

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
> <mailto: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 <mailto: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
> <mailto: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 <mailto: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 <mailto: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
> <mailto: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 <mailto: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