[patch] SequenceManagerMySQLImpl

[John M <wu...@mailandnews.com>]

I had some real slowness inserting records into a large table.  Turns out that 
with the existing query mySQL returned the last id once for every row in the 
table, which is pretty slow when there are 100000 records.  mySQL doesn't 
really care about the table - there's only one last id at any time for a given 
connection.  I just changed the query to "SELECT LAST_INSERT_ID()".

With the mySQL connector the Statement class has a method to get the last id, 
which might be more efficient, but I didn't think it was worth it to force the 
inclusion of mysql jars in the build just for this one class to reference 
them.

John Marshall
Connectria

PS Is this still the best class to use for mySQL autoincrement pks?  It's not 
in the tests, but it seems that it's the only one that would work (other than 
using OJB generated keys).


Here's what I changed - pretty basic:

cvs diff SequenceManagerMySQLImpl.java (in directory 
D:\DEV\jakarta\db-ojb\src\java\org\apache\ojb\broker\util\sequence\)
Index: SequenceManagerMySQLImpl.java
===================================================================
RCS file: 
/home/cvspublic/db-ojb/src/java/org/apache/ojb/broker/util/sequence/SequenceMa
nagerMySQLImpl.java,v
retrieving revision 1.11
diff -r1.11 SequenceManagerMySQLImpl.java
185c185
< 				rsStmt = dbAccess.executeSQL("SELECT LAST_INSERT_ID() as newid FROM " + 
cld.getFullTableName(), cld, Query.NOT_SCROLLABLE);
---
>                 rsStmt = dbAccess.executeSQL("SELECT LAST_INSERT_ID()", cld, 
Query.NOT_SCROLLABLE);
189c189
<                     newid = rsStmt.m_rs.getInt("newid");
---
>                     newid = rsStmt.m_rs.getInt(1);


---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org