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.
Was this topic helpful?
Document Information
Modified date:
23 June 2018
UID
swg21414662