Speeding up ESQL fetches

Hi, I am trying to speed up a program by fetching a row into a sqlda structure instead of using EXEC SQL get descriptor 'demodesc' value :i … for every column. So far so good, I can use it just fine to directly assign simple values into my structures. When it comes to datetime types I am somewhat lost though. I would like to fetch it as char/string as described in "implicit conversion" on https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.esqlc.doc/ sii061001534.htm since that would match what the program would use later on (and is currently doing via individual get descriptor calls). I cannot find a way to do this with a sqlda struct (which probably makes sense, but maybe I miss something). Am I missing something or do I really need to create a large fetch buffer, send everything there and then convert the data to fit my application needs? Thanks, Florian

It's been a while since I did this, but doesn't the select statement have and "into" host descriptor that you define. If so, you should be able to define the "into" descriptor so that the data for datetime would be converted into a character string. But I don't understand why you think this would perform any faster than using a basic PREPARED SQL statement. The purpose of using descriptors is if the client application has no before hand knowledge of the data type that the select statement will be returning. When I wrote cdr check, I had to use descriptors because we had to deal with all data types that the select statement would be based on how the user defined the replicated row. Everything had to be dynamic. Internally you prepare a dynamic query using descriptors in pretty much the same way that you prepare a select statement. It's just that by using descriptors, you are able to define the data types being returned at runtime rather than at compile time. I don't see how performance would be affected at all by using descriptors because internally it's pretty much the same execution as a static SQL statement. Madison Pruet Retired and Loving it On Tuesday, August 30, 2016 4:03 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Hi, I am trying to speed up a program by fetching a row into a sqlda structure instead of using EXEC SQL get descriptor 'demodesc' value :i for every column. So far so good, I can use it just fine to directly assign simple values into my structures. When it comes to datetime types I am somewhat lost though. I would like to fetch it as char/string as described in "implicit conversion" on https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.esqlc.doc/ sii061001534.htm since that would match what the program would use later on (and is currently doing via individual get descriptor calls). I cannot find a way to do this with a sqlda struct (which probably makes sense, but maybe I miss something). Am I missing something or do I really need to create a large fetch buffer, send everything there and then convert the data to fit my application needs? Thanks, Florian ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I have nothing to add, but I am curious to know how much performance improvement you expect to get from doing this. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FLORIAN APOLLONER Sent: Tuesday, August 30, 2016 4:03 PM To: ids@iiug.org Subject: Speeding up ESQL fetches [37679] Hi, I am trying to speed up a program by fetching a row into a sqlda structure instead of using EXEC SQL get descriptor 'demodesc' value :i . for every column. So far so good, I can use it just fine to directly assign simple values into my structures. When it comes to datetime types I am somewhat lost though. I would like to fetch it as char/string as described in "implicit conversion" on https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.esqlc. doc/sii061001534.htm since that would match what the program would use later on (and is currently doing via individual get descriptor calls). I cannot find a way to do this with a sqlda struct (which probably makes sense, but maybe I miss something). Am I missing something or do I really need to create a large fetch buffer, send everything there and then convert the data to fit my application needs? Thanks, Florian **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

If you are working inside the engine using UDRs, then as general rule running the data in MI_BINARY is usually quite a bit quicker. Just a lot harder to debug :) Cheers Paul > I have nothing to add, but I am curious to know how much performance > improvement you expect to get from doing this. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > FLORIAN APOLLONER > Sent: Tuesday, August 30, 2016 4:03 PM > To: ids@iiug.org > Subject: Speeding up ESQL fetches [37679] > > Hi, > > I am trying to speed up a program by fetching a row into a sqlda structure > instead of using EXEC SQL get descriptor 'demodesc' value :i . for every > column. So far so good, I can use it just fine to directly assign simple > values into my structures. When it comes to datetime types I am somewhat > lost though. I would like to fetch it as char/string as described in > "implicit conversion" on > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.esqlc. > doc/sii061001534.htm > since that would match what the program would use later on (and is > currently > doing via individual get descriptor calls). I cannot find a way to do this > with a sqlda struct (which probably makes sense, but maybe I miss > something). > Am I missing something or do I really need to create a large fetch buffer, > send everything there and then convert the data to fit my application > needs? > > Thanks, > Florian > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

Assuming that you are wanting to use descriptors because your query text must be dynamic.... 1) Prepare the cursor for the text 2) Describe the prepared statement into a variable defined as struct sqlda *dq_ptr. 3) The value of dq_ptr->sqld is the number of items in the descriptor array 4) for (int i=1; i < dq_ptr->sqld; i++) dq_ptr->sqlvar[i-1] is the sqlvar_struct for that column.... This is described in the version 12 manual in chapter 17 starting at page 17-14... Madison Pruet Retired and Loving it On Tuesday, August 30, 2016 4:03 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Hi, I am trying to speed up a program by fetching a row into a sqlda structure instead of using EXEC SQL get descriptor 'demodesc' value :i for every column. So far so good, I can use it just fine to directly assign simple values into my structures. When it comes to datetime types I am somewhat lost though. I would like to fetch it as char/string as described in "implicit conversion" on https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.esqlc.doc/ sii061001534.htm since that would match what the program would use later on (and is currently doing via individual get descriptor calls). I cannot find a way to do this with a sqlda struct (which probably makes sense, but maybe I miss something). Am I missing something or do I really need to create a large fetch buffer, send everything there and then convert the data to fit my application needs? Thanks, Florian ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

OK, here's what you have to do. After the DESCRIBE to populate the sqlda structure: - Go to the sqlda->sqlvar[colno-1] (the ifx_sqlvar_t struct for the DATETIME column. - Modify the sqltype field to SQLCHAR or SQLSTRING - Assign sufficient memory to the sqldata field (don't forget space for the trailing NULL if you used SQLSTRING above) - Set the sqllen field to the size of the memory allocated for the sqldata field - Continue as you have been - OPEN the cursor as usual and FETCH BTW, for even faster fetching of multiple rows look into ARRAY FETCHING (although there is a bug in the array fetch code that will refuse to fetch LVARCHAR columns with a spurious error about not using ARRAY FETCH with DELAYED PREPARE). Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 30, 2016 at 5:03 PM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Hi, > > I am trying to speed up a program by fetching a row into a sqlda structure > instead of using EXEC SQL get descriptor 'demodesc' value :i for every > column. So far so good, I can use it just fine to directly assign simple > values into my structures. When it comes to datetime types I am somewhat > lost > though. I would like to fetch it as char/string as described in "implicit > conversion" on > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11. > 50.0/com.ibm.esqlc.doc/sii061001534.htm > since that would match what the program would use later on (and is > currently > doing via individual get descriptor calls). I cannot find a way to do this > with a sqlda struct (which probably makes sense, but maybe I miss > something). > Am I missing something or do I really need to create a large fetch buffer, > send everything there and then convert the data to fit my application > needs? > > Thanks, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1140b160f52181053b51f018

Oops, the types to set should be CCHARTYPE or CSTRINGTYPE not SQLCHAR or SQLSTRING. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Aug 30, 2016 at 6:59 PM, Art Kagel <art.kagel@gmail.com> wrote: > OK, here's what you have to do. After the DESCRIBE to populate the sqlda > structure: > > - Go to the sqlda->sqlvar[colno-1] (the ifx_sqlvar_t struct for the > DATETIME column. > - Modify the sqltype field to SQLCHAR or SQLSTRING > - Assign sufficient memory to the sqldata field (don't forget space > for the trailing NULL if you used SQLSTRING above) > - Set the sqllen field to the size of the memory allocated for the > sqldata field > - Continue as you have been > - OPEN the cursor as usual and FETCH > > BTW, for even faster fetching of multiple rows look into ARRAY FETCHING > (although there is a bug in the array fetch code that will refuse to fetch > LVARCHAR columns with a spurious error about not using ARRAY FETCH with > DELAYED PREPARE). > > Art > > > Art S. Kagel, President and Principal Consultant > ASK Database Management > www.askdbmgt.com > > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on the IIUG, nor any other organization with which I am > associated either explicitly, implicitly, or by inference. Neither do > those opinions reflect those of other individuals affiliated with any > entity with which I am affiliated nor those of the entities themselves. > > > On Tue, Aug 30, 2016 at 5:03 PM, FLORIAN APOLLONER < > florian.apolloner@bap.at> wrote: > >> Hi, >> >> I am trying to speed up a program by fetching a row into a sqlda structure >> instead of using EXEC SQL get descriptor 'demodesc' value :i for every >> column. So far so good, I can use it just fine to directly assign simple >> values into my structures. When it comes to datetime types I am somewhat >> lost >> though. I would like to fetch it as char/string as described in "implicit >> conversion" on >> https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50. >> 0/com.ibm.esqlc.doc/sii061001534.htm >> since that would match what the program would use later on (and is >> currently >> doing via individual get descriptor calls). I cannot find a way to do this >> with a sqlda struct (which probably makes sense, but maybe I miss >> something). >> Am I missing something or do I really need to create a large fetch buffer, >> send everything there and then convert the data to fit my application >> needs? >> >> Thanks, >> Florian >> >> >> ************************************************************ >> ******************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > --001a1144c0d660a0e0053b520c35

Hi Madison & Andrew, to answer your question about the expected performance improvements. To be blunt, I have no idea. All I know is that over >10e6 rows with a table of roughly 100 columns EXEC SQL FETCH :cur INTO :data1:indi1, :data2:indi2 performs quite a bit better than my "dynamic" approach of EXEC SQL GET DESCRIPTOR :descFetch_c VALUE :i :data1 = DATA, :indi1 = INDICATOR; Valgrind's callgrind seems to suggest that most time is spent in my GET DESCRIPTOR calls, I was hoping that by using an sqlda structure where the pointers do not change, I could utilize EXEC SQL FETCH :cur INTO DESCRIPTOR and get similar performance (I will report back once I know more). What makes you think that performance would not increase? Thanks, Florian

Hi Art, wow -- you are spot on as always! > - Modify the sqltype field to SQLCHAR or SQLSTRING Damn, I thought I am not allowed to touch those, but that makes sense -- I will try that! > - OPEN the cursor as usual and FETCH Is it important that I open the cursor after I did above changes, or is it just important that I do that before the FETCH? > BTW, for even faster fetching of multiple rows look into ARRAY FETCHING I tried once and failed horribly, maybe at some day in the future (when I actually fully understand ESQL) I can do that. Thank you & cheers, Florian

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } I don't think you got my point. Why are you using descriptors at all? Sent from Yahoo Mail for iPad On Wednesday, August 31, 2016, 2:02 AM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Hi Madison & Andrew, to answer your question about the expected performance improvements. To be blunt, I have no idea. All I know is that over >10e6 rows with a table of roughly 100 columns EXEC SQL FETCH :cur INTO :data1:indi1, :data2:indi2 performs quite a bit better than my "dynamic" approach of EXEC SQL GET DESCRIPTOR :descFetch_c VALUE :i :data1 = DATA, :indi1 = INDICATOR; Valgrind's callgrind seems to suggest that most time is spent in my GET DESCRIPTOR calls, I was hoping that by using an sqlda structure where the pointers do not change, I could utilize EXEC SQL FETCH :cur INTO DESCRIPTOR and get similar performance (I will report back once I know more). What makes you think that performance would not increase? Thanks, Florian ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Because the queries are dynamically generated and I neither know the select clause nor the input params at compile time.

ok - then you have to use descriptors. Yes, you can modify the descriptor of the receiving buffers. You might also want to check into setting the environment variable FET_BUF_SIZE to improve performance. It is described in the Guide to SQL manual. FET_BUF_SIZE can be used to increase the size of the network buffer used to transfer data from the server to the client and can reduce the number of network messaged required to complete the data transfer because more data (i.e. rows) will be contained in a single network message. Madison Pruet Retired and Loving it On Wednesday, August 31, 2016 3:43 AM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Because the queries are dynamically generated and I neither know the select clause nor the input params at compile time. ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Florian: For an example of array fetching take a look at the code for my dbcopy.ec utility. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 31, 2016 at 3:05 AM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Hi Art, > > wow -- you are spot on as always! > > > - Modify the sqltype field to SQLCHAR or SQLSTRING > > Damn, I thought I am not allowed to touch those, but that makes sense -- I > will try that! > > > - OPEN the cursor as usual and FETCH > > Is it important that I open the cursor after I did above changes, or is it > just important that I do that before the FETCH? > > > BTW, for even faster fetching of multiple rows look into ARRAY FETCHING > > I tried once and failed horribly, maybe at some day in the future (when I > actually fully understand ESQL) I can do that. > > Thank you & cheers, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114fa8ea998a75053b5ceec5

Thank you, will do. So far I think I've found a few culprits in my code and managed to get a nice speed up. One thing I am still playing with though: We have a function which does FETCH twice, once two fetch the data and the second time to verify that it indeed only returned one row. Can I somehow figure out if there is another row waiting without actually calling FETCH? Cheers, Florian

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } Not really, but that's not an issue. The network buffers are used in a streaming manner. The rows are buddy-bunched so that multiple network messages are contained in the buffer. If there are no more rows to fetch, then all you really doing with the second fetch is checking the next item in the buffer (which you have already received). If it is an EOT, then there are no more rows. That is why there is a performance advantage to increasing the size of the FET_BUFF_SIZE. Sent from Yahoo Mail for iPad On Wednesday, August 31, 2016, 8:28 AM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Thank you, will do. So far I think I've found a few culprits in my code and managed to get a nice speed up. One thing I am still playing with though: We have a function which does FETCH twice, once two fetch the data and the second time to verify that it indeed only returned one row. Can I somehow figure out if there is another row waiting without actually calling FETCH? Cheers, Florian ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

I'm sure performance will increase, I was just interested in this because it was a different kind of performance tuning than we usually see around here. The performance tuning talk around here is usually about tuning the engine, adding an appropriate index for a query, updating statistics, changing the fetch buffer size, setting OPTOFC, etc. You're looking to shave 0.0000001 seconds off something that runs 1000000000 times/day vs shaving 30 seconds off something that runs 100 times/day and that it interesting. Andrew -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of FLORIAN APOLLONER Sent: Wednesday, August 31, 2016 2:02 AM To: ids@iiug.org Subject: Re: RE: Speeding up ESQL fetches [37686] Hi Madison & Andrew, to answer your question about the expected performance improvements. To be blunt, I have no idea. All I know is that over >10e6 rows with a table of roughly 100 columns EXEC SQL FETCH :cur INTO :data1:indi1, :data2:indi2 . performs quite a bit better than my "dynamic" approach of EXEC SQL GET DESCRIPTOR :descFetch_c VALUE :i :data1 = DATA, :indi1 = INDICATOR; Valgrind's callgrind seems to suggest that most time is spent in my GET DESCRIPTOR calls, I was hoping that by using an sqlda structure where the pointers do not change, I could utilize EXEC SQL FETCH :cur INTO DESCRIPTOR and get similar performance (I will report back once I know more). What makes you think that performance would not increase? Thanks, Florian **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

My issues is/was that the second FETCH would also reinitialize buffers (my code actually), so I restructured a few methods and everything is fine now :D

Hehe, I get that -- but the existing code performed so badly in some circumstance that I could increase performance by a factor of two at least in most cases. While not the easiest and low-hanging fruit, it also gave me a better understanding of ESQL etc, so that is a plus too

I would also looke at OPTOFC, which optimizes the network round trips on open fetch close. It can take those three network round trips and drop them to a single network round trip. It works very well with FET=5FBUF=5FSIZE mentioned below by Madison. There is also= a FREE optimization. John F. Miller III miller3@us.ibm.com 503-747-1366 ids-bounces@iiug.org wrote on 08/31/2016 02:11:51 AM: > From: "Madison Pruet" <madison=5Fpruet@yahoo.com> > To: ids@iiug.org > Date: 08/31/2016 02:12 AM > Subject: Re: RE: Speeding up ESQL fetches [37690] > Sent by: ids-bounces@iiug.org > > ok - then you have to use descriptors. Yes, you can modify the descriptor of > the receiving buffers. You might also want to check into setting the > environment variable FET=5FBUF=5FSIZE to improve performance. It is descr= ibed in > the Guide to SQL manual. > > FET=5FBUF=5FSIZE can be used to increase the size of the network buffer u= sed to > transfer data from the server to the client and can reduce the number of > network messaged required to complete the data transfer because more data > (i.e. rows) will be contained in a single network message. > Madison Pruet > Retired and Loving it > > On Wednesday, August 31, 2016 3:43 AM, FLORIAN APOLLONER > <florian.apolloner@bap.at> wrote: > > Because the queries are dynamically generated and I neither know the select > clause nor the input params at compile time. > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ***************************************************************************= **** > Forum Note: Use "Reply" to post a response in the discussion forum. >

AFAIK the only way to know if there are more rows waiting to be fetched is to try a FETCH and see if the SQLCODE that is returned is 100 (NOTFOUND). An alternative would be to execute an equivalent SELECT COUNT(*) before hand to know the actual number of rows. But that's more expensive. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 31, 2016 at 9:28 AM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Thank you, will do. So far I think I've found a few culprits in my code and > managed to get a nice speed up. One thing I am still playing with though: > We > have a function which does FETCH twice, once two fetch the data and the > second > time to verify that it indeed only returned one row. Can I somehow figure > out > if there is another row waiting without actually calling FETCH? > > Cheers, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a114fa8eac44e9e053b5f1f0c

Yeah, that's what I figured too. I restructured my code so that another fetch does not cause any harm :D Cheers, Florian

OPTOFC is the one thing I cannot use currently since the application is too large and just crashes if I activate that (have to rewrite all the code first to support it…)

You can turn it on for individual statements. John F. Miller III miller3@us.ibm.com 503-747-1366 ids-bounces@iiug.org wrote on 08/31/2016 12:57:37 PM: > From: "FLORIAN APOLLONER" <florian.apolloner@bap.at> > To: ids@iiug.org > Date: 08/31/2016 12:58 PM > Subject: Re: RE: Speeding up ESQL fetches [37702] > Sent by: ids-bounces@iiug.org > > OPTOFC is the one thing I cannot use currently since the application is too > large and just crashes if I activate that (have to rewrite all the code first > to support it…) > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Sorry, that message displays as base64!? gibberish

You will need the secret key from John to make it work > OPTOFC is the one thing I cannot use currently since the application is > too > large and just crashes if I activate that (have to rewrite all the code > first > to support it…) > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians

Do you expect there to be multiple rows returned? How wide are the tables in the database. FET_BUFF_SIZE can make a big difference if the rows tend to be wide (greater than 2K bytes) or if you expect to receive multiple rows. The only down size is that the environment variable must be set prior to database connection and it will require more memory on the database size to hold the larger buffer size. So you need to consider the total number of connections that you will have as compared to your available memory. Madison Pruet Retired and Loving it On Wednesday, August 31, 2016 2:57 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: OPTOFC is the one thing I cannot use currently since the application is too large and just crashes if I activate that (have to rewrite all the code first to support it) ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Reading the docs again, I think I might have mixed up defered-prepare with OPTOFC. We are already successfully using FET_BUFF_SIZE if I am not mistaken (though we do set it directly in the code instead of as env variable).

It needs to be in the environment prior to connecting to the database. Also what is it set to? Madison Pruet Retired and Loving it On Wednesday, August 31, 2016 3:19 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Reading the docs again, I think I might have mixed up defered-prepare with OPTOFC. We are already successfully using FET_BUFF_SIZE if I am not mistaken (though we do set it directly in the code instead of as env variable). ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Actually there are environment variables that control FETBUFSIZ and OPTOFC, but there are also esql global C variables that control this behavior. Changing the global variables before each statement can control where these features are used. John F. Miller III miller3@us.ibm.com 503-747-1366 From: "Madison Pruet" <madison=5Fpruet@yahoo.com> To: ids@iiug.org Date: 08/31/2016 01:24 PM Subject: Re: RE: Speeding up ESQL fetches [37709] Sent by: ids-bounces@iiug.org It needs to be in the environment prior to connecting to the database. Also what is it set to? Madison Pruet Retired and Loving it On Wednesday, August 31, 2016 3:19 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Reading the docs again, I think I might have mixed up defered-prepare with OPTOFC. We are already successfully using FET=5FBUFF=5FSIZE if I am not mistaken (though we do set it directly in the code instead of as env variable). ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum. ***************************************************************************= **** Forum Note: Use "Reply" to post a response in the discussion forum.

The environment variable lets you set the buffer size up to 4GB while the internal global variable is a type short limiting the buffer size that you can define using it to 32K. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 31, 2016 at 4:19 PM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Reading the docs again, I think I might have mixed up defered-prepare with > OPTOFC. We are already successfully using FET_BUFF_SIZE if I am not > mistaken > (though we do set it directly in the code instead of as env variable). > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c05bce8fd0798053b6478ac

Ok, I just tried implementing fetch arrays and the win is marginal on a first glimpse -- I guess I will have to try and see what a proper combination of fetch array sizes and fetch buffer is (and use https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11.50.0/com.ibm.esqlc.doc/ sii-15-41466.htm#sii-15-41466 to check if I still have proper sizes). That said, the fact that FetArraySize is global seems kinda limiting; from my first tries, it seems that I will have to adjust everything to work with the fetch arrays?

FetArraySize takes effect at the time that a cursor is opened, so just set it to zero after opening the one cursor that you want to use array fetching and other cursors should behave normally returning one row at a time. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 31, 2016 at 5:23 PM, FLORIAN APOLLONER <florian.apolloner@bap.at > wrote: > Ok, I just tried implementing fetch arrays and the win is marginal on a > first > glimpse -- I guess I will have to try and see what a proper combination of > fetch array sizes and fetch buffer is (and use > https://www.ibm.com/support/knowledgecenter/en/SSGU8G_11. > 50.0/com.ibm.esqlc.doc/sii-15-41466.htm#sii-15-41466 > to check if I still have proper sizes). That said, the fact that > FetArraySize > is global seems kinda limiting; from my first tries, it seems that I will > have > to adjust everything to work with the fetch arrays? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1143e20074e249053b64e136

Mhm, unless I just did something wrong, it seems to take effect here during FETCH and not OPEN, could that be?

Maybe I misunderstood. Always assumed open time. Art On Aug 31, 2016 18:39, "FLORIAN APOLLONER" <florian.apolloner@bap.at> wrote: > Mhm, unless I just did something wrong, it seems to take effect here during > FETCH and not OPEN, could that be? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --94eb2c0b170a0acf04053b66eacd

I think that the key to making your fetch faster is to make sure that your FETBUFF is large enough to hold multiple rows. So check the length of your row against the value your are using for FETBUFF. If for instance, your row width is 10K, but the FETBUFF is only 4K, then you would need three network transmissions (really 6 due to SQLI protocol) just to get a single row transferred from the server to the client application. If you size FETBUFF large enough, then you can approximate the performance of an array fetch. Madison Pruet Retired and Loving it On Wednesday, August 31, 2016 5:39 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Mhm, unless I just did something wrong, it seems to take effect here during FETCH and not OPEN, could that be? ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi Madison, I've just been playing with FetArrSize and wrote code like this: FetArrSize = fetchSize_; EXEC SQL FETCH :some_cursor USING DESCRIPTOR sqlDataPtr_; if (FetArrSize != fetchSize_) { fetchSize_ = FetArrSize; } FetArrSize = 0; My rowsize is 37 and if I set fetchSize_ to something >885 it gets capped back to 885, so there seems to be a fetch buffer in effect of roughly 32745 bytes -- this does make sense since FetBufSize in incl/esql/sqlhdr.h is int2 -- so 2**16 / 2 = 32768 bytes would be the maximum allowed size. Interestingly enough if I output FetBufSize or BigFetBufSize it is always 4096. Adding to that, no matter what I set FET_BUF_SIZE or BIG_FET_BUF_SIZE env variables to, I cannot get over those 32k, any ideas where it goes wrong? Thanks, Florian

Florian: While the FET_BUF_SIZE environment variable can be set to values up to 2GB in v11.70 and later (CSDK v4.70+), that only works for "normal" fetching. Apparently array fetching is still limited to 32K according to the documentation and my own testing. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 1, 2016 at 4:17 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: > Hi Madison, > > I've just been playing with FetArrSize and wrote code like this: > > FetArrSize = fetchSize_; > EXEC SQL FETCH :some_cursor USING DESCRIPTOR sqlDataPtr_; > if (FetArrSize != fetchSize_) { > > fetchSize_ = FetArrSize; > } > FetArrSize = 0; > > My rowsize is 37 and if I set fetchSize_ to something >885 it gets capped > back > to 885, so there seems to be a fetch buffer in effect of roughly 32745 > bytes > -- this does make sense since FetBufSize in incl/esql/sqlhdr.h is int2 -- > so > 2**16 / 2 = 32768 bytes would be the maximum allowed size. Interestingly > enough if I output FetBufSize or BigFetBufSize it is always 4096. > > Adding to that, no matter what I set FET_BUF_SIZE or BIG_FET_BUF_SIZE env > variables to, I cannot get over those 32k, any ideas where it goes wrong? > > Thanks, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0102f15c531212053b78120f

Oh wow, that explains a lot. So I guess I have to choose between array fetching but be limited by the buffer vs bigger buffer and single fetches. My initial tests show that both variants play at a similar speed with a properly set FET_BUF_SIZE, so I am wondering if array fetching is worth it. I will try to rerun my tests over a high latency connection next week and see what I can come up with, cause all test for now have been via localhost where network speed is not really an issue ;) Thanks & Cheers, Florian

I have not found much performance improvement from buffer sizes larger than about 1MB, but please share your own testing. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 1, 2016 at 4:43 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: > Oh wow, that explains a lot. So I guess I have to choose between array > fetching but be limited by the buffer vs bigger buffer and single fetches. > My > initial tests show that both variants play at a similar speed with a > properly > set FET_BUF_SIZE, so I am wondering if array fetching is worth it. > > I will try to rerun my tests over a high latency connection next week and > see > what I can come up with, cause all test for now have been via localhost > where > network speed is not really an issue ;) > > Thanks & Cheers, > Florian > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1146fb7e94fb46053b7860d6

If you are on current software and use the environmental variable, you should be able to use a fetch buffer up to 2G. (I can't remember if it is a signed integer or unsigned. If unsigned, then 4G. ) Madison Pruet Retired and Loving it On Thursday, September 1, 2016 3:17 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Hi Madison, I've just been playing with FetArrSize and wrote code like this: FetArrSize = fetchSize_; EXEC SQL FETCH :some_cursor USING DESCRIPTOR sqlDataPtr_; if (FetArrSize != fetchSize_) { fetchSize_ = FetArrSize; } FetArrSize = 0; My rowsize is 37 and if I set fetchSize_ to something >885 it gets capped back to 885, so there seems to be a fetch buffer in effect of roughly 32745 bytes -- this does make sense since FetBufSize in incl/esql/sqlhdr.h is int2 -- so 2**16 / 2 = 32768 bytes would be the maximum allowed size. Interestingly enough if I output FetBufSize or BigFetBufSize it is always 4096. Adding to that, no matter what I set FET_BUF_SIZE or BIG_FET_BUF_SIZE env variables to, I cannot get over those 32k, any ideas where it goes wrong? Thanks, Florian ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Me neither, (on localhost at least) the sweet spot currently seems to be around the 1MB you mentioned and there my array fetching code seems to perform worse for this specific query. I'll really see that I can gather some high latency results next week, though I fear that a bigger buf size will help even more there!

blockquote, div.yahoo_quoted { margin-left: 0 !important; border-left:1px #715FFA solid !important; padding-left:1ex !important; background-color:white !important; } I think that you will see a signifanct difference once you go across the wire. When the client and server is on the same machine you are using local loop back and barely touching the network at all. Sent from Yahoo Mail for iPad On Thursday, September 1, 2016, 4:09 PM, FLORIAN APOLLONER <florian.apolloner@bap.at> wrote: Me neither, (on localhost at least) the sweet spot currently seems to be around the 1MB you mentioned and there my array fetching code seems to perform worse for this specific query. I'll really see that I can gather some high latency results next week, though I fear that a bigger buf size will help even more there! ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.