IBM Support

Improve Client and Server communication performace.

Question & Answer


Question

How to improve connection performance when client application connection IDS server.

Cause


Informix dynamic server communicate with client using SQLI message as following process.Enviorment variable FET_BUF_SIZE can override the default buffer except large object.


Client sends message to server
Server processes message
Server sends response
Client sends next message....
.....

Answer


The SQLI protocol, on which all client-server communications are based.
Reduce the round trips by optimizing message transfers is mainly tuning method as below mentioned example.


60KB of data returned from select.Each row returned is less than 4K, and there are no blobs.

1. Don't set FET_BUF_SIZE, and default buffer size is 4K.
PREPARE stm FROM PREP/DECLARE -- Msg Sent/Received
“select * from t where a > key”;
DECLARE cursor1 from :stm;
OPEN cursor1 USING var1; OPEN -- Msg Sent/Received
FETCH cursor1 into results1; FETCH -- 16 Msgs Sent/Received
CLOSE cursor1; CLOSE -- Msg Sent/Received
FREE cursor1; FREE -- Msg Sent/Received
FREE stm;


2. Set FET_BUF_SIZE to 16KB

PREPARE stm FROM PREP/DECLARE -- Msg Sent/Received
“select * from t where a > ?”;
DECLARE cursor1 from :stm;
OPEN cursor1 USING var1; OPEN -- Msg Sent/Received
FETCH cursor1 into results1; FETCH -- 4 Msgs Sent/Received
CLOSE cursor1; CLOSE -- Msg Sent/Received
FREE cursor1; FREE -- Msg Sent/Received
FREE stm;

As above example mentioned,you can see the number of Fetch messages reduced to 4 round-trips (from 16 previously),so you can increasing the size of the Fet_buf to reduce network traffic between your Client program and the database to improve application connection performance.

3. SET OPTOFC = 1

Reduces the number of SQLI message round trips.

[{"Product":{"code":"SSGU8G","label":"Informix Servers"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"10.0;11.1;7.3;9.4;11.5","Edition":"Enterprise","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
23 June 2018

UID

swg21414662