Today I replaced some database code in Storefront 5.0 with some stored procedures. The Storefront code was creating a recordset just to add a new record. I suspect it was selecting all the records, 9889 records in this case, and trying to pull a lot of data from the database when all it meant to do was add one more record. I saw an immediate performance improvement when I replaced the code to insert new order detail records with my stored procedure code.
The reason Storefront used that inefficient method was to get the ID number of the new record that was created without doing another query. But a stored procedure that does an insert can return the new ID number using the SCOPE_IDENTITY() function. SCOPE_IDENTITY() returns the last IDENTITY value inserted into an IDENTITY column in the same scope.
