IDS 11.70 slowdown due to TABLEAU tool
Chanaka Perera reported an IDS 11.70 slowdown when Tableau ran a huge SELECT over ODBC and asked if IWA would help. SangGyu Jeong suggested raising the ODBC fetch buffer size. Paul Watson found maxing the buffer unhelpful. Martin Fuerderer explained IWA mainly helps heavy OLAP queries returning few rows. No outcome was reported.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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
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 ------------------------------
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
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") at http://www.redbooks.ibm.com/abstracts/sg248150.html?Open Regards, Martin -- ::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.