FET_BUF_SIZE or How do I optimize cursor reads?

I am trying to optimize the performance of an ESQL/C application. I was planning on changing some of my standard open cursor-fetch-close cursor cycles to fetch array cursors (fetch mycursor using descriptor). But when I read about the FET_BUF_SIZE environment variable I started wondering if simply setting this variable to a high value (32767) would turn on some kind of automatic array fetching by Informix. Is this true or would I have to recode my application to use fetch arrays to see some performance gain? Any help would be greatly appreciated. Thanks and Cheers Eckard

In article <8iqgv9$b0h$1@pollux.ip-plus.net>, "EA" <appshome@yahoo.com> wrote: > I am trying to optimize the performance of an ESQL/C application. I was > planning on changing some of my standard open cursor-fetch-close cursor > cycles to fetch array cursors (fetch mycursor using descriptor). But when I > read about the FET_BUF_SIZE environment variable I started wondering if > simply setting this variable to a high value (32767) would turn on some kind > of automatic array fetching by Informix. Is this true or would I have to > recode my application to use fetch arrays to see some performance gain? Any > help would be greatly appreciated. > > Thanks and > Cheers > Eckard > > Performance tuning is always a challenge. Just by increasing the FET_BUF_SIZE to the maximum necessarily does not guarantee a very good performance. I do not believe that there is any automatic array fetching associated with FET_BUF_SIZE. Since you mentioned arrays it means that you are using multiple rows of data. The best choice is to use select or insert cursors. Try setting OPTOFC=1 which is supposed to reduce cursor messages. In other words if OPTOFC is enabled the server will open the cursor on the first fetch and close the cursor after the last row is fetched. This eliminates message passing for the OPEN and CLOSE statments. Use SET AUTOFREE...ENABLED. RTFM for the exact syntax. This notifies the server that the cursor memory should be freed automatically when the cursor is closed and eliminates the message passing otherwise required by the use of FREE statement. Good luck. Ram S. Sent via Deja.com http://www.deja.com/ Before you buy.

Please see comments below. ram_cnc wrote: > In article <8iqgv9$b0h$1@pollux.ip-plus.net>, > "EA" <appshome@yahoo.com> wrote: > > I am trying to optimize the performance of an ESQL/C application. I > was > > planning on changing some of my standard open cursor-fetch-close > cursor > > cycles to fetch array cursors (fetch mycursor using descriptor). But > when I > > read about the FET_BUF_SIZE environment variable I started wondering > if > > simply setting this variable to a high value (32767) would turn on > some kind > > of automatic array fetching by Informix. Is this true or would I have > to > > recode my application to use fetch arrays to see some performance > gain? Any > > help would be greatly appreciated. > > > > Thanks and > > Cheers > > Eckard > > > > > Performance tuning is always a challenge. Just by increasing the > FET_BUF_SIZE to the maximum necessarily does not guarantee a very good > performance. But most likely it will improve performance. > I do not believe that there is any automatic array fetching associated > with FET_BUF_SIZE. The frontend usualy fetches multiple rows at a time (completely transparent to the application). FetBufSize allows you to tune how much buffer space you are willing to provide. FetBufSize gives you some control on the 'automatic array fetching' that IS performed by the front end libraries. > Since you mentioned arrays it means that you are using multiple rows of > data. The best choice is to use select or insert cursors. Obviously, the whole discussion applies only to select and insert cursors (otherwise there wouldn't be multiple rows). Performance both for select and insert cursors can be tuned using FetBufSize. > Try setting OPTOFC=1 which is supposed to reduce cursor messages. In > other words if OPTOFC is enabled the server will open the cursor on the > first fetch and close the cursor after the last row is fetched. This > eliminates message passing for the OPEN and CLOSE statments. Correct. > Use SET AUTOFREE...ENABLED. RTFM for the exact syntax. This notifies > the server that the cursor memory should be freed automatically when > the cursor is closed and eliminates the message passing otherwise > required by the use of FREE statement. This is not a good idea if an application reuses a previously prepared statement. Be careful using the autofree feature. Hope this helps, Heiko > > > Good luck. > > Ram S. > > Sent via Deja.com http://www.deja.com/ > Before you buy.

EA wrote: OK here's the complete poop. Informix ALWAYS transfers a COM BUFFER's worth of data from the server to the application. The ESQL or CLI library handle unpacking this buffer and returning a single row to a FETCH operation. Each COM BUFFER transfer requires an acknowledgement from the client so increasing the buffer's size from the default of 4K to say the max of 32767 using FET_BUF_SIZE or the equivalent global variable, will reduce the overhead of these acknowledgements by a factor of 8. A savings, yes, and greater for FETCHing than for INSERT CURSORs which use the same COM BUFFER sizing for caching PUTs. To Heiko, reread the manual, FetBufSize is ONLY effective BEFORE the first cursor is opened, at that time the ESQL library sets the buffer sizing for the life of the session. To set different buffer sizing for different cursors you MIGHT try separate sessions but I'mnot certain that will help. FETCH ARRAY makes a big improvement in operating speed, especially with 32K fetch buffering. To Obnoxio, look at the code for dbcopy.ec, it always uses fetch array and optionally block flushes single rows (without -F) or the entire set of FETCHED rows in a single FLUSH (with -F). Dbcopy runs 3x faster doing block flushes on than without. How much of the gain is just due to block flushing rather than individual row flushing I can't say but there must be some gain. > I am trying to optimize the performance of an ESQL/C application. I was > planning on changing some of my standard open cursor-fetch-close cursor > cycles to fetch array cursors (fetch mycursor using descriptor). But when I > read about the FET_BUF_SIZE environment variable I started wondering if > simply setting this variable to a high value (32767) would turn on some kind > of automatic array fetching by Informix. Is this true or would I have to > recode my application to use fetch arrays to see some performance gain? Any > help would be greatly appreciated. Art S. Kagel