Re: retrieving auto-generated keys on bulk insert

[Jeff Mathis]

The code that we have has a low-level save() method that all database
objects use which issues a select statement after a new row insert to
get the auto-generated key (id in our case). Doing this extra select
statement after an insert really does not add overhead -- I timed it
with and without and saw no reproducible difference. I hadn't thought
about using an updatable ResultSet, mainly because I'm still in the dark
ages (I'll look into it though).

By far, the biggest performance boost we see is to make sure auto-commit
is off and then issue comit statements only after a few thousand inserts
or so.

jeff

Christopher Taylor wrote:
> 
> Not to get into an English parsing war here, but there's also the word
> "any", which means some (including the singular).
> 
> Unless Mark can work some magic with the MySQL protocol, there's no way
> (AFAIK) that this can be done... unless he parses the statement and issues
> INSERT calls one at a time, fetching the LAST_INSERT_ID() on each operation.
> Not very efficient, but it might just work...
> 
> -Chris
> 
> ----- Original Message -----
> From: "Dane Foster" <dfoster@stripped>
> To: "Paul DuBois" <paul@stripped>
> Cc: <java@stripped>
> Sent: Wednesday, January 08, 2003 4:20 PM
> Subject: Re: retrieving auto-generated keys on bulk insert
> 
> > Paul DuBois wrote:
> >
> > "It certainly does.  The behavior you describe below is how INSERT is
> > documented in the manual to work with respect to LAST_INSERT_ID()
> > and multiple-row inserts -- that is, LAST_INSERT_ID() returns the
> > *first* auto-generated ID value.
> >
> > This isn't a JDBD issue."
> >
> > I would like to point out that yes it is a JDBC issue because (the
> following is
> > copied from the JDBC javadoc):
> > <copy>
> > getGeneratedKeys
> > public ResultSet getGeneratedKeys()throws SQLExceptionRetrieves any
> > auto-generated keys created as a result of executing this Statement
> object. If
> > this Statement object did not generate any keys, an empty ResultSet object
> is
> > returned.
> >
> >
> >     Returns:
> >     a ResultSet object containing the auto-generated key(s) generated by
> the
> > execution of this Statement object
> >     Throws:
> >     SQLException - if a database access error occurs
> >     Since:
> >     1.4
> > </copy>
> > Notice that the word key is plural, meaning if a statement causes the
> creating
> > of more than one key then the ResultSet object should return all keys, not
> just
> > the first key.  So from the JDBC perspective the MySQL JDBC driver's
> > implementation of the getGeneratedKeys method is not consistent with the
> JDBC
> > documentation.
> >
> > Dane Foster
> > Equity Technology Group, Inc.
> > http://www.equitytg.com
> > 954.360.9800
> > ----- Original Message -----
> > From: "Paul DuBois" <paul@stripped>
> > To: "Dane Foster" <dfoster@stripped>; <java@stripped>
> > Sent: Tuesday, January 07, 2003 23:31
> > Subject: Re: retrieving auto-generated keys on bulk insert
> >
> >
> > At 16:41 -0500 1/7/03, Dane Foster wrote:
> > >Hi Mark.
> > >
> > >The Connector/J JDBC driver does not correctly handle returning
> auto-generated
> > >keys on multi row inserts.
> >
> > It certainly does.  The behavior you describe below is how INSERT is
> > documented in the manual to work with respect to LAST_INSERT_ID()
> > and multiple-row inserts -- that is, LAST_INSERT_ID() returns the
> > *first* auto-generated ID value.
> >
> > This isn't a JDBD issue.
> >
> > >  I'm not sure if this is a bug because the multi row
> > >INSERT syntax in MySQL is MySQL specific therefore the JDBC rules
> > >may not apply.
> > >On the other hand, the ANSI/ISO SQL standard supports multi row inserts,
> and
> > >since the JDBC API is geared towards the ANSI/ISO SQL standard maybe the
> JDBC
> > >rules do apply and it is indeed a bug.
> > >
> > >Let my back up for a moment and clarify in case I lost anyone.  Lets
> > >pretend we
> > >have a database table named 'MY_TABLE' that looks something like this:
> > >
> > >CREATE TABLE MY_TABLE(
> > >pk    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
> > >misc  VARCHAR (255),
> > >PRIMARY KEY (pk));
> > >
> > >Using Connector/J I have code similar to:
> > ><code>
> > >String sql = "INSERT INTO MY_TABLE (misc) VALUES ('Test')";
> > >Statement st = connection.createStatement();
> > >
> > >st.executeUpdate( sql );
> > >ResultSet rs = st.getGeneratedKeys();
> > >
> > >if( rs.next() )
> > >     System.out.println( "The auto-generated key is " + rs.getLong(
> 1 ) );
> > >else
> > >     throw new IllegalStateException( "No auto-generated key found." );
> > ></code>
> > >
> > >For a newly created 'MY_TABLE' table the code snippet above would
> produce:
> > >The auto-generated key is 1
> > >
> > >The next snippet of code is where the problem lies.
> > ><code>
> > >String sql = "INSERT INTO MY_TABLE (misc) VALUES ('Test1'),('Test2')";
> > >Statement st = connection.createStatement();
> > >
> > >st.executeUpdate( sql );
> > >ResultSet rs = st.getGeneratedKeys();
> > >
> > >while( rs.next() )
> > >     System.out.println( "The auto-generated key is " + rs.getLong(
> 1 ) );
> > ></code>
> > >
> > >For a newly created 'MY_TABLE' table the code snippet would produce:
> > >The auto-generated key is 1
> > >
> > >If you missed it; There is no second line of output!
> > >
> > >Mark, please get back to me about this when you can.  It's kind of
> > >important for
> > >a project that I'm working on.  I would make recommendations on a
> > >fix but I know
> > >absolutely zilch about the MySQL network protocol.  Thanx.
> > >
> > >
> > >Dane Foster
> > >Equity Technology Group, Inc.
> > >http://www.equitytg.com
> > >954.360.9800
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/doc/         (the manual)
> >    http://lists.mysql.com/           (the list archive)
> >
> > To request this thread, e-mail <java-thread4855@stripped>
> > To unsubscribe, e-mail
> <java-unsubscribe-dfoster=equitytg.com@stripped>
> >
> >
> >
> > ---------------------------------------------------------------------
> > Before posting, please check:
> >    http://www.mysql.com/doc/         (the manual)
> >    http://lists.mysql.com/           (the list archive)
> >
> > To request this thread, e-mail <java-thread4856@stripped>
> > To unsubscribe, e-mail
> <java-unsubscribe-cstaylor=nanshu.com@stripped>
> >
> >
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/doc/         (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <java-thread4857@stripped>
> To unsubscribe, e-mail <java-unsubscribe-jmathis=predict.com@stripped>

-- 
Jeff Mathis, Ph.D.			505-995-1434
The Prediction Company			jmathis@stripped
525 Camino de los Marquez, Ste 6	http://www.predict.com
Santa Fe, NM 87505