Wednesday, August 20, 2008

Delphi Insert vs Append Performance

For well over 10 years I have thought that the only difference between the Delphi TDataSet.Insert method and TDataSet.Append was where the record appeared in data aware controls before it was actually posted.

For most cases that is true. In fact if you look at the Delphi documentation you will find the method descriptions are almost identical and they both include the following:

"After the new record is applied back to the database server, its physical location is database-specific. For indexed tables, the index is updated with the new record information."

In some user interface situations I can certainly see a use for the Insert method, but I almost always use the Append method, especially when writing batch operations that insert a number of records.

There is a subtle implementation difference in the Delphi TDataSet code (the Delphi code, not the Advantage descendant code) that can have a rather dramatic affect on performance. A call to Append sets an EOF flag in the internal record buffer. A call to Insert does not. The internal EOF flag results in a call to TDataSet.Last before EVERY Post call. A call to Last results in an extra trip to the server. Not a big deal for a single insert, but in a batch insert operation from the client this can have a huge impact on performance, especially if the data is located on a remote machine.

In a very quick test I appended 10,000 records to a table using an Advantage remote server connection to a different PC (not the Advantage server on my test PC, I wanted the network traffic). I ran the test once using Append and once using Insert. I'm sure you can see where I'm going with this... Append took almost twice as long as Insert!

Append: 8359 ms
Insert: 4409 ms

Batch inserts are best performed via SQL, but I know lots of applications process them a row at a time using Append. If you have code that does this it might be worth your time to try testing with Insert instead.

No comments:

Post a Comment