SQL Server Interface Performance

Performance enhancements have been made to the SQL server interface.

Overview:

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, then it is updated. If someone changed the record since it was read, the REWRITE or DELETE will return F. 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 it is being done twice. Also, that means that changes made to the record set by other processes 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 real file, not the locally cached version.

NOTE: When using one of these new Hold Types on a job that contains a Query process followed by an Update or Output that uses that query, make sure that the Hold Type is the same on both the Query and the process that uses that Query.

Furthermore, APPX 5.2 now supports SQL Server 2012.

Comments:

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

-- JeanNeron - 2012-10-30