 |
INFORMIX-ESQL/C Programmer's Manual Using Dynamic SQL
|
|
Optimizing OPEN, FETCH, and CLOSE
When an ESQL/C application uses DECLARE and OPEN statements to execute a cursor, each statement involves a round trip of message requests between the application and the database server. The optimize-OPEN-FETCH-CLOSE feature (OPTOFC) reduces the number of round trips by two, as follows:
Restrictions on OPTOFC
With the OPTOFC feature enabled, the following restrictions exist:
- You can only use the OPTOFC feature on select cursors whose SELECT statement was prepared. For example, the OPTOFC feature reduces network round trips for the following select cursor:
- The OPTOFC feature eliminates execution of the OPEN statement as a separate step. Therefore, any error conditions that opening the cursor might generate are not returned until after the initial FETCH.
- Static cursors are not freed when they are closed.
- With the OPTOFC feature enabled, neither static nor dynamic cursors are freed when they are closed. Because ESQL/C does not actually send the CLOSE statement to the database server, a cursor is not implicitly freed. A subsequent OPEN and FETCH on a cursor actually opens the same cursor. Only at this time would the database server notice if the table was modified (if it was dropped, altered, or renamed), in which case it generates an error (-710).
- With the OPTOFC feature disabled, a static cursor is freed when it is closed. When ESQL/C reaches a CLOSE statement for a static cursor, it actually sends a message to close the cursor and free memory associated with this cursor. However, dynamic cursors are not implicitly freed when they are closed.
- The GET DIAGNOSTICS statement does not work for SQL statements that are delayed on the way to the database server. For example, in the following sequence of SQL statements, GET DIAGNOSTICS returns 0, indicating success, even though the OPEN is delayed until the first fetch:
Enabling the OPTOFC Feature
The OPTOFC environment variable enables the OPTOFC feature. You can assign the following values to the OPTOFC environment variable.
The default value of the OPTOFC environment variable is 0 (zero). If you set this environment variable from the shell, make sure you set it before you start the ESQL/C application.
On UNIX operating systems, you can set OPTOFC in the application with the putenv() system call (as long as your system supports the putenv() function). For example, the following call to putenv() enables the OPTOFC feature:
In Windows environments, you can use the ifx_putenv() function.
With putenv() or ifx_putenv(), you can activate or deactivate the OPTOFC feature for each connection or within each thread. You must call the putenv() or ifx_putenv() function before you establish a connection.
Important: Informix utilities do not support the IFX_AUTOFREE, OPTOFC, and IFX_DEFERRED_PREPARE environment variables. Use these environment variables only with INFORMIX-ESQL/C client applications.
INFORMIX-ESQL/C Programmer's Manual, Version 9.21
Copyright © 1999, Informix Software, Inc. All rights reserved