On 11/3/05, Charles Harvey III <[EMAIL PROTECTED]> wrote: > Wow. Figured it out. This goes back to my "Impossibly long insert > time for one object" e-mail conversation from last week. > > I actually found a bug. > > > org.apache.ojb.broker.platforms.PlatformMsSQLServerImpl > > This method to be exact: > getLastInsertIdentityQuery(java.lang.String tableName) > > It returns a string that says: > "SELECT @@IDENTITY FROM " + tableName; > > This, is in fact, incorrect. > > It should be: > "SELECT @@IDENTITY"; > > > Depending on table size, this query can run for MINUTES. As it > was doing for me. Try it out. Open up a query browser and > run this: > > INSERT INTO TABLE (...) VALUES (...) SELECT @@IDENTITY FROM TABLE > > And then compare to this: > > INSERT INTO TABLE (...) VALUES (...) SELECT @@IDENTITY > > Its silly, but the difference is huge. The first one scans every > row in the database, printing out 'null' for every row, then spits > out the last identity at the end. In a table of 9 million rows, this > ran for 15 minutes. Take the "FROM TABLE" off and it pulls the > identity value from a system table and returns in .0001 ms. > > So. What do I do now? I modified my local version and re-deployed. > Problem solved. Whew. No more locking, socket closings and applications > falling over and shutting down. > > Is there a place where I can attach a bug report? Can anyone else > test this out to make sure I'm not nuts (I'm 99.99% I'm not).
Sure, just add one in OJB's JIRA: http://issues.apache.org/jira/browse/OJB if you happen to have a Sql Server 2000, could you test the IDENT_CURRENT method SELECT IDENT_CURRENT(<tableName>) it would have the benefit of returning the last inserted id for the specified table, not for all tables as does @@IDENTITY (which might be problematic in case of triggers etc.). Tom --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
