ODBC - optimising performance - use of the writekl command and avoiding unnecessary unlocks

Optimising performance with ODBC - using the writekl command and avoiding unnecessary unlocks

The following code is slow with SQL Server because the SQL Server write command does not automatically unlock the record. Consequently, after each write, Sculptor has to send an extra unlock to be compatible with Sculptor keyed files. This not only unlocks the record but also releases the logical record set, which then gets rebuilt by the subsequent next command.

while (TRUE) {
    next MYFILE nsr=BREAK
    ...
    write MYFILE
    ...
}

Since the SQL Server next command unlocks the previous record, the enforced unlock is not needed in the above code, so Sculptor has introduced the writekl (write keep lock) command. Replacing the above code with the code below increases performance:

while (TRUE) {
    next MYFILE nsr=BREAK
    ...
    writekl MYFILE
    ...
}
unlock MYFILE

Note that an unlock command has been added after the loop. Although the nsr trap unlocks the last record, this unlock is necessary in case the loop breaks for any other reason, such as a conditional test. Even if not strictly necessary, it’s wise to have the final unlock in case the code is later modified.

In order to achieve acceptable performance with the next, prev, find and match commands, Sculptor selects a logical record set from a SQL database and then fetches the records as required. To unlock the current record in SQL Server, either the next record must be fetched or the record set must be closed. An unlock command closes the record set, which must then be rebuilt. This is slow. To avoid this, existing code should be checked and unnecessary unlock commands removed. In the following example, the unlock command serves no purpose inside the loop because the record will be unlocked when the next record is fetched. As in the previous example, a single unlock command after the loop is sufficient.

 while (TRUE) {
      next MYFILE nsr=BREAK
      ...
      if (condition) break
      ...
      if (condition) {
           write MYFILE
      } else {
           unlock MYFILE
      }
}

The above loop should be re-coded as follows. Note that the unlock command has been moved outside the loop and the write command has been replaced by writekl.

while (TRUE) {
    next MYFILE nsr=BREAK
    ...
    if (condition) break
    ...
    if (condition) {
         writekl MYFILE
    }
}
unlock MYFILE

---

RELATED TOPICS

ODBC

Optimising performance with ODBC