Improve Client and Server communication performace.

Technote (FAQ)


This document applies only to the following language version(s):

English

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.

Related information

FET_BUF_SIZE
Information center

Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

Informix Servers

Software version:

7.3, 9.4, 10.0, 11.1, 11.5

Operating system(s):

AIX, HP-UX, Linux, Solaris

Software edition:

Enterprise

Reference #:

1414662

Modified date:

2011-10-25

Translate my page

Machine Translation

Content navigation