Informix

 View Only
  • 1.  IDS 11.70 slowdown due to TABLEAU tool

    Posted Fri March 13, 2020 11:26 AM
    Hi,

    In this incident TABLEAU connected directly to IDS 11.70 database via ODBC connection.
    They have proceeds a SELECT query millions of records and we have notices it will affected to other database users.

    We have noticed this issue only once, is above scenario will slowdown IDS server ?
    How can we resolve this issue ?

    I found below URL " Using Tableau with IBM Informix" , which describe about Informix Warehouse Accelerator (IWA)  
    https://community.tableau.com/ideas/3402

    Is IWA is a solution for this issue ?
    Could you please reply with explanation ? 


    Thanks
    Chanaka Perera

    ------------------------------
    Chanaka Perera
    ------------------------------

    #Informix


  • 2.  RE: IDS 11.70 slowdown due to TABLEAU tool

    Posted Sat March 14, 2020 12:35 PM
    Hello Chanaka,
    Increasing the fetch buffer size in the Informix ODBC configuration can help improve SELECT statement execution performance.
    Below is Informix ODBC configuration screen in Windows environment. Fetch Buffer Size can be set up to 2147483647 (2GB).

    Related setting parameters are described in the link below.
    https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.sqlr.doc/ids_sqr_235.htm

    There is a good post on fetch buffer on Ben's blog, so I recommend reading it.
    https://informixdba.wordpress.com/2017/04/03/improving-remote-query-performance-by-tuning-fet_buf_size/


    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 3.  RE: IDS 11.70 slowdown due to TABLEAU tool

    IBM Champion
    Posted Sat March 14, 2020 01:56 PM
    My testing with Java/12.10 showed that max'ing the buffer was not a good
    thing, the fun part was finding the sweet point for the system or just
    setting dynamically based on the anticipated query return size

    Cheers
    Paul

    > Hello Chanaka,
    > Increasing the fetch buffer size in the Informix ODBC configuration can
    > help improve SELECT statement execution performance.
    > Below is Informix ODBC configuration screen in Windows environment. Fetch
    > Buffer Size can be set up to 2147483647 (2GB).
    >
    >
    > Related setting parameters are described in the link below.
    > https://www.ibm.com/support/knowledgecenter/SSGU8G_11.70.0/com.ibm.sqlr.doc/ids_sqr_235.htm
    >
    #Informix


  • 4.  RE: IDS 11.70 slowdown due to TABLEAU tool

    Posted Mon March 16, 2020 04:24 PM
    Hi Chanaka,

    whether and how much a query can benefit from IWA depends very much on the query itself.
    Typically, the queries that benefit most are OLAP-type queries. These usually are quite complicated,
    require the database server to examine millions of rows, possibly do some sort of aggregation, but
    then produce few result records. The processing of such queries often not only causes a lot of I/O,
    like whole table scans, but also is CPU intensive. 

    With IWA, such a query can benefit because IWA keeps all required data in memory, hence no I/O
    is required. And with a lot of CPU power available on the IWA machine (or even an IWA cluster), the
    CPU intensive processing can be completed faster. As usually few data records are produced as
    result, little network bandwidth is consumed by transferring such small result sets from IWA to the
    Informix server (and on to the database client).

    When such "heavy queries" get off-loaded from the Informix server machine to the IWA machine(s),
    other users will benefit as well, because the Informix server itself is not busy with executing the
    query. It lets IWA do the heavy lifting.

    Having said this, it also becomes clear that not all types of queries can benefit (much) from IWA.
    If, for instance, the Informix server is able to use an index to retrieve few required data records from
    a huge table, then this may even be faster than with IWA acceleration. Also, queries that return a
    large result set to the database client may not benefit from IWA, because of the overhead of
    transferring such large result sets from IWA to the Informix server.

    From the rather short description you have given, it seems that the customer's scenario very much
    looks like the latter case: a query that produces a large result set?
    Of course, with such queries it may be possible to look at the application and its concept and figure
    out, whether and how it is possible to transfer processing of large result sets from the application
    into the database server - and from there to IWA ...

    I hope this short answer can help. For more in depth information, I would like to recommend the
    "IWA Redbook" (title "Query Acceleration for Business using IBM Informix Warehouse Accelerator")

    Regards, Martin
    --
    Martin Fuerderer
    Informix Development Germany


    HCL Technologies Germany GmbH
    Frankfurter Ring 17
    80807 Munich, Germany
    http://www.hcltech.com/de

    ::DISCLAIMER::

    The contents of this e-mail and any attachment(s) are confidential and intended for the named recipient(s) only. E-mail transmission is not guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or may contain viruses in transmission. The e mail and its contents (with or without referred errors) shall therefore not attach any liability on the originator or HCL or its affiliates. Views or opinions, if any, presented in this email are solely those of the author and may not necessarily reflect the views or opinions of HCL or its affiliates. Any form of reproduction, dissemination, copying, disclosure, modification, distribution and / or publication of this message without the prior written consent of authorized representative of HCL is strictly prohibited. If you have received this email in error please delete it and notify the sender immediately. Before opening any email and/or attachments, please check them for viruses and other defects.