2012 Unexpected Degeneration of Insert_Recordset Into TempDb Buffer

I recently discovered an interesting degeneration of insert_recordset into a TempDb buffer with huge performance implications.  While I refer only to insert_record in this post, the results apply equally to update_recordset and delete_recordset also.

When used properly, insert_recordset can yield huge performance gains over X++ code loops that execute individual insert statements because the kernel will generate and execute a single SQL statement that allows SQL Server to do the heavy lifting.  An important consideration is whether the target table of the insert_recordset statement has an override of the insert() method.  If so, the kernel will degenerate the insert_recordset into a code loop so that the insert() method can fire for each record.  This degeneration can be avoided with the .skipDataMethods(true) method on the table buffer prior to the insert_recordset command.  All of this is well documented and commonly understood by experienced developers.

This post illustrates a completely different case of degeneration of insert_recordset.

Here I've created a project with a single form, named MyTempDbForm, and a single class, named MyTempDbFormHelper.  In the form, add a data source, and reference it to TmpInventAge which is a TempDb table already available in the AOT.



Since I need to insert data into TmpInventAge, I've created a server static method on MyTempDbFormHelper class that accepts a TmpInventAge buffer by parameter and simply copies the ItemId field from all InventSum records into TmpInventAge records.



Then I call that method from the executeQuery() method on the form's data source, just prior to the super() call, passing the data source buffer by parameter.



When I open the MyTempDbForm, instead of executing a single SQL statement which should complete nearly instantly, the kernel executes more than 5,000 SQL statements!  This is easily seen with the Trace cockpit, as shown below.  Note the number of Database Calls and the long string of ::doInsert() calls on the TempDb table.



Since TmpInventAge does not override the insert() method, what caused the insert_recordset statement to degenerate into all of those individual SQL statements?

The problem it seems has to do entirely with the use of a client buffer (created by the form data source on the client tier) on the server.  I don't think it entirely makes sense, but with this understanding it can be easily fixed.

The solution is to create a server buffer of TmpInventAge, link it to the client buffer that was passed to the insertTempDbData method (using the linkPhysicalTableInstance() method), and execute the insert_recordset statement against that server buffer instead.



Again using the Trace cockpit shown below, notice the single Database Call and no more ::doInsert() calls, as well as the dramatic improvement in the Inclusive response time.