db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Armin Waibel (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (OJB-77) PlatformMsSQLServerImpl.getLastInsertIdentityQuery creates incorrect SQL statement to pull the identity from the last inserted row
Date Fri, 11 Nov 2005 16:30:03 GMT
     [ http://issues.apache.org/jira/browse/OJB-77?page=all ]
     
Armin Waibel resolved OJB-77:
-----------------------------

    Fix Version: 1.0.4
     Resolution: Fixed

I agree with you, the documentation recommends to use "SELECT SCOPE_IDENTITY()" to get the
latest identity value of the current session and scope:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_6n8p.asp


> 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
>      Fix For: 1.0.4

>
> 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