Informix

Expand all | Collapse all

Informix Client CSDK Parameters

  • 1.  Informix Client CSDK Parameters

    Posted Mon July 26, 2021 12:49 PM
    Hi All,

    I am trying to improve network performance between client server comunication.  I am not finding documentation about Parameters on Infomix Client CSDK to try improve this comunication ?  (like parameters to improve TCPIP Buffers,  etc...).. now 2/3 of time is spending in network comunication.....

    Would like to play with that ...  some one has some documentation ? link ??

     Tks All

    Miguel
    SkypeID MiguelCarbone
    +55 11 996347103

    ------------------------------
    Miguel Carbone
    ------------------------------


  • 2.  RE: Informix Client CSDK Parameters

    Posted Mon July 26, 2021 01:44 PM
    Miguel:

    Several things you can do:
    • Increase the size of the communications buffer (default 4K) either
      • Set the global variable fet_buf_size (up to 32K) in your code, or
      • Set the environment variable FET_BUF_SIZE (up to 2GB)
        This can be a biggie when wide rows or many many rows will be returned to the application or sent to the engine using PUT cursors.
    • Enable "optimized message transfers" aka message chaining:
      • Set the OPTMSG environment variable to 1 to enable message chaining, then in your code
      • Set the global variable OptMsg = 1 to enable all subsequent SQL statements, set to 0 to disable for subsequent SQL.
      • Be sure to disable message chaining (OptMsg = 0) before the last SQL statement prior to closing the database connection to ensure that all queued messages have been sent to the engine before the application exits.
    • SET OPTOFC to 1 in the environment (you can use putenv( "OPTOFC=1" ) in your code) to enable optimized cursor opening and closing. Defers the opening of a cursor until the first fetch and closes and frees the cursor when the last row is fetched from it.
    • SET IFX_AUTOFREE to 1 in the environment to automatically free cursors when they are closed.
    • SET IFX_DEFERRED_PREPARE to 1 in the environment to defer the PREPARE of a statement until the statement is executed or the CURSOR declared on it is opened.
    All of these are ways to reduce the number of round trip messages between the engine and the application. There are some interactions between them and gotcha's to watch out for. Also doing things like PREPARE once and reuse a statement id many times improves performance more than just using OPTPFC or IFX_DEFERRED_PREPARE and preparing the same text over and over. All documented in the ESQL/C manual and online manual pages.

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Informix Client CSDK Parameters

    Posted Tue July 27, 2021 06:07 AM
    Hi Miguel,

    A while ago we had a case with such an issue. As a result, a new environment variable was created that can be used to set the transfer buffer size for TEXT data types. For us, the variable was built into the .NET provider and ODBC. I don't know if it works for other connection types.

    For the transfer of TEXT/BLOB data types there is a fixed size of 1kB. With IFX_TEXT_XFERSIZE the size can be set. With IFX_TEXT_XFERSIZE=32000 we could reduce the time from 180sec to 10sec in our example (insert of 5MB text).

    The variable exists in CSDK versions from 4.10.xC12.

    Hope this helps.

    ------------------------------
    Andreas Seifert
    CURSOR Software AG
    http://www.admin-scout.com
    ------------------------------