Thursday, May 13, 2010

OLTP Benchmark - OPTOFC

I had always intended to try OPTOFC and the other settings that help reduce network traffic between the clients and the server, but upon the advice from Cesar and The Clown I decided to try them now. I'll admit that I didn't expect this setting to be the breakthrough setting I was looking for, but it sure was!

OPTOFC is an environment variable that you set on the client that optimizes the opening, fetching and closing of cursors by deferring the opening of a cursor until the first fetch is executed and automatically closing the cursor once the last row is returned to the client. In the benchmark I'm doing a lot of single row lookups, before this would be 3 network messages. 1 to open the cursor, another to fetch the row and a third to close the cursor. With OPTOFC set this is reduced to 1 network message that opens, fetches the 1 row and closes the cursor and boy howdy did it do the trick by increasing my TPM to 9607! That's an improvement of 31.5%, not bad for a simple environment variable change.

10,000 TPM is now within reach.

Without the 3 cheat codes from the previous post I was able to reach a TPM of 7855, a 27% improvement.


  1. So, do you want to put up a summary of what you have done and what your onconfig looks like? It might inspire some other ideas. ;o)

  2. Very interesting that using OPTOFC will give such an increase. Is there any drawbacks in using it ?
    As far as I can understand there is not, should it not be set always then ?