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.<\/p>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading