IDS Experts

I will reiterate few existing features in Informix which you can use for optimize the connection to database. It is always an expensive operation when establishing a connection to a database. The goal should be that applications perform as many operations as possible while connected to database. Following are some connection attributes that can be use to tune application performance by reducing the network traffic:

Bulk insert optimization (SQL_ENABLE_INSERT_CURSOR):  Create an insert cursor is a good idea to efficiently insert rows into a table in bulk. You need to set the SQL_ENABLE_INSERT_CURSOR attribute with QLSetStmtOption(), then call SQLParamOptions() with the number of rows as a parameter. An Insert cursor creates a buffer in memory to hold a block of rows. The buffer receives data from the application program; once buffer is full data send to the database server in a block. The buffer reduces the amount of communication between the program and the database server. As a result, the insertions go faster.

Optimize message transfer (OPTMSG): This feature allows you to minimize message transfers between the database server and application. Application can optimized message transfers by chaining messages together and even eliminating some small message packets. You can enable this feature by setting   OPTMSG environment variable at application side.  Open fetch close optimization (OPTOFC): 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 open-fetch-close optimization (OPTOFC) reduces the number of round trips network traffic. With this feature, the OPEN statement from application does not open the cursor immediately. Instead, it saves any input value that was supplied in the USING clause of the OPEN statement. Next time along with the FETCH statement it sends this input value. The database server opens the cursor and returns the first value in this cursor. Once database server reaches the last value of an open cursor, it automatically closes the cursor after it sends the data to the client application. You can use the OPTOFC environment variable to enable this feature.

 I just provide some touch point here, there are several other way to improve network and connection to database. Please check the Improve Application Performance in Informix Infocenter for details.

 

-Sanjit Chakraborty