Re: FET_BUF_SIZE or How do I optimize cursor reads?

From: "EA" <appshome@yahoo.com> > >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. Well it *is* all in the manual, you know. :0) FET_BUF_SIZE doesn't do an array fetch as such, but it does fill a buffer full of records before sending it down the network, which may or may not help performance depending on how your network is set up. As a test I wrote a program to generate 1 million records and stick them in the database. Singleton inserts took 18:07.97, an insert cursor took 1:13.77 and the biggest FET_BUF_SIZE took 1:07.59. I haven't tested reads yet, but it didn't seem to make huge odds on writes.... Have you looked at OPTOFC? ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com

> Well it *is* all in the manual, you know. :0) True, but for once I just couldn't figure it out from the Informix manuals (which I find very well written for the most part, btw) because fetch arrays and FET_BUF_SIZE are covered in separate unrelated chapters. I blame it on the unusually hot weather in central Europe today, not having air conditioning... > Have you looked at OPTOFC? I have looked at OPTOFC but I felt that saving 2 roundtrips for opening and closing my cursors wouldn't make a big difference as these cursors typically select a couple of hundred or thousand records (which I should have mentioned in my original posting...) > FET_BUF_SIZE doesn't do an array fetch as such, but it does fill a buffer > full of records before sending it down the network By "before sending it down the network" you obviously refer to your insert cursor test program. But in the case of a select cursor that buffer would still be on the client side (quoted from the manual: "In a client-server environment, you must set the cursor buffer size on the client side of the application because this buffer resides in the application process.") So I assume the server actually sends more than row at a time, up to as much as will fit in the client side buffer, and a fetch will be served from that buffer first before a new set of rows will have to be sent from the server. This is exactly what I would implement myself using array fetches. So I am still wondering if it is worth the while... Thanks again and Cheers Eckard

Eckard, array fetch will provide additional benefit (compared to using FetBufSize) only when blob data is involved. FetBufSize does not optimize network traffic when blobs are involved, array fetch does. Using the global variable FetBufSize in ESQL/C programs you can define your network buffer on a per cursor basis (i.e. the value of FetBufSize at prepare/declare will be used for all further operations for that cursor). Hope this helps, Heiko EA wrote: > > Well it *is* all in the manual, you know. :0) > > True, but for once I just couldn't figure it out from the Informix manuals > (which I find very well written for the most part, btw) because fetch arrays > and FET_BUF_SIZE are covered in separate unrelated chapters. I blame it on > the unusually hot weather in central Europe today, not having air > conditioning... > > > Have you looked at OPTOFC? > > I have looked at OPTOFC but I felt that saving 2 roundtrips for opening and > closing my cursors wouldn't make a big difference as these cursors typically > select a couple of hundred or thousand records (which I should have > mentioned in my original posting...) > > > FET_BUF_SIZE doesn't do an array fetch as such, but it does fill a buffer > > full of records before sending it down the network > > By "before sending it down the network" you obviously refer to your insert > cursor test program. But in the case of a select cursor that buffer would > still be on the client side (quoted from the manual: "In a client-server > environment, you must set the cursor buffer size on the client side of the > application because this buffer resides in the application process.") So I > assume the server actually sends more than row at a time, up to as much as > will fit in the client side buffer, and a fetch will be served from that > buffer first before a new set of rows will have to be sent from the server. > This is exactly what I would implement myself using array fetches. So I am > still wondering if it is worth the while... > > Thanks again and > Cheers > Eckard