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] Commented: (OJB-77) PlatformMsSQLServerImpl.getLastInsertIdentityQuery creates incorrect SQL statement to pull the identity from the last inserted row
Date Thu, 03 Nov 2005 23:15:09 GMT
    [ http://issues.apache.org/jira/browse/OJB-77?page=comments#action_12356728 ] 

Charles N. Harvey commented on OJB-77:
--------------------------------------

Actually, the SQL 2000 documentation says to use a different (new) function to get the identity.

old:  INSERT INTO TABLE (...) VALUES (...) SELECT @@IDENTITY

new:  INSERT INTO TABLE (...) VALUES (...) SELECT SCOPE_IDENTITY()




> 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