SQL Server Interface Performance

Performance enhancements have been made to the SQL server interface.

Overview:

Korry explains that it should be tested because it is possible that using these new Hold Types could create slower performance. Here's why.

Type 3: Defer locking records until an Update or Delete is done. Locking a record ahead of time is very expensive for performance. Locking every record one at a time when an update or delete to that record may never happen is slow. This hold type will not actually lock the record but if you attempt to rewrite or delete it then it is locked and the record checked against what it was when Appx originally read it. If the record was not changed out from under us then we update it. If we detect that someone changed the record we fail on the rewrite or delete with a F flag. This should have no negative performance issues but you may have to change your programs to handle a Failed rewrite or delete (ie, save your changed fields, reread the record, restore your changed fields & try again).

Type 4: Firehose cursors. This hold type also includes the deferred lock. A firehose cursor allows APPX to transfer the entire result set of the SQL request at one time. This is very fast compared to the old way of reading one record at a time as needed. The record set is cached locally in the APPXPATH and continued reads to the file will be made to this cache file. However, this could be slower if the client/server model is already doing that silently, then we are doing it twice. Also, that means that changes made to the record set will not be reflected in the data being read by the BEG READ, Query, etc. since it will be processing from a snapshot. Rewrites and deletes will go against the read file.

Comments:

Read what other users have said about this page or add your own comments.

<--/commentPlugin-->

-- JeanNeron - 2012-10-30