db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Charles N. Harvey (JIRA)" <j...@apache.org>
Subject [jira] Created: (OJB-77) PlatformMsSQLServerImpl.getLastInsertIdentityQuery creates incorrect SQL statement to pull the identity from the last inserted row
Date Thu, 03 Nov 2005 23:03:48 GMT
PlatformMsSQLServerImpl.getLastInsertIdentityQuery creates incorrect SQL statement to pull
the identity from the last inserted row
----------------------------------------------------------------------------------------------------------------------------------

         Key: OJB-77
         URL: http://issues.apache.org/jira/browse/OJB-77
     Project: OJB
        Type: Bug
  Components: PB-API  
    Versions: 1.0.3    
 Environment: Microsoft SQL 2000 server
    Reporter: Charles N. Harvey


org.apache.ojb.broker.platforms.PlatformMsSQLServerImpl.getLastInsertIdentityQuery(java.lang.String
tableName)

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.


-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira


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


Mime
View raw message