Skip to Content
SAP Advantage Database Server

What is the preferred method to add records to table?

With local server, I think that the SQL statement would never actually be faster (although the difference may be negligible in most cases). With remote server, the SQL statement is probably the best case in general.

AdsAppendRecord involves two requests to the server.  The first request obtains the record information including record number, auto-increment info, etc.  The second request writes the field values and unlocks the record. Thus, in the client/server situation this can be the primary cost (the SQL statement would use only one server request ... well ... more on that in a bit).  With local server, the multiple requests are not an issue (they are just function calls) and on a fast LAN and especially when client and server are on the same box using shared memory to communicate, it is also not much of an issue with remote server.

There is some cost in parsing the the SQL statement, but not a lot.  If multiple records are being added, the SQL overhead can be eliminated by using a prepared statement.  And this also raises the possibility I alluded to for multiple round trips to the server with SQL.  If you create a brand new SQL statement, insert one record, and then close the statement, I think there are at least two round trips to the server.

The number of fields might produce a small different in a client/server situation. If you are writing a very large record but only setting one or two fields in it during the append, then the SQL may end up sending a lot less data across the wire.  The API AdsAppendRecord will send the entire record buffer across the wire.  Note, though, that this is typically not going to make much of a difference since it is usually the round trip cost that is expensive rather than the amount of data (although a flaky WAN might prove to be the exception here).

No comments