Does INET cache rows for fetching?
Question: does an ESQL-C fetch loop over a cursor require a network round trip per row (SE 5.10 / I-Net 5.10)? Answer: no. Informix ships rows in blocks via a communications buffer — fixed at 4K in 5.x, tunable 4K to 32K-1 in 6.x and later — and the engine pre-fills the next buffer while the client works through the current one, with buffering on both engine and client sides. To speed things up, raise the buffer with FET_BUF_SIZE / FetBufSize (set before PREPARE/DECLARE, per cursor) and use ARRAY FETCH; gains are largest over slow TCP links, smaller for shared memory. A follow-up asking where such documentation lives went unanswered.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am wondering if I write and ESQL-C application that has a loop fetching through an opened cursor does INET perform any caching of records so that each fetch does not require a round-trip cycle to the server? I am using SE 5.10 and INET 5.10 on the server side.
All Informix FETCH communications involves sending a block or rows to the client. In 5.xx that communications buffer size is fixed at 4K, in 6.xx, 7.xx, 8.xx, & 9.xx it is adjustable from 4K to 32K-1. The engine is filling the next buffer in the background for you so it will be ready when needed. Art S. Kagel TN wrote: > > I am wondering if I write and ESQL-C application > that has a loop fetching through an opened cursor > does INET perform any caching of records so > that each fetch does not require a round-trip > cycle to the server? I am using SE 5.10 and > INET 5.10 on the server side.
Out of interest, does anyone know where these buffers live? I have an application that in part gets many rows from a single table and plots the data. As an exercise to speed this up, I tried replacing the select/fetch/etc routines with a function to return some simple synthetic data, and was surprised to find that the application didn't run much faster. My conclusion was that the 'real' data was being buffered very effectively by Informix, but was it buffered in the engine, or in my client application? Also, is it buffered in the same way for shared memory, streams and socket connection methods? Many thanks, Andy. In article <39B517F3.CEC7E665@bloomberg.net>, "Art S. Kagel" <kagel@bloomberg.net> writes >All Informix FETCH communications involves sending a block or rows to the >client. In 5.xx that communications buffer size is fixed at 4K, in 6.xx, >7.xx, 8.xx, & 9.xx it is adjustable from 4K to 32K-1. The engine is filling >the next buffer in the background for you so it will be ready when needed. > >Art S. Kagel > >TN wrote: >> >> I am wondering if I write and ESQL-C application >> that has a loop fetching through an opened cursor >> does INET perform any caching of records so >> that each fetch does not require a round-trip >> cycle to the server? I am using SE 5.10 and >> INET 5.10 on the server side. Andrew Lennard andy@kontron.demon.co.uk
And i ask how can i free these bufferes? Andy Lennard wrote: > Out of interest, does anyone know where these buffers live? > > I have an application that in part gets many rows from a single table > and plots the data. As an exercise to speed this up, I tried replacing > the select/fetch/etc routines with a function to return some simple > synthetic data, and was surprised to find that the application didn't > run much faster. > > My conclusion was that the 'real' data was being buffered very > effectively by Informix, but was it buffered in the engine, or in my > client application? > > Also, is it buffered in the same way for shared memory, streams and > socket connection methods? > > Many thanks, > Andy. > > In article <39B517F3.CEC7E665@bloomberg.net>, "Art S. Kagel" > <kagel@bloomberg.net> writes > >All Informix FETCH communications involves sending a block or rows to the > >client. In 5.xx that communications buffer size is fixed at 4K, in 6.xx, > >7.xx, 8.xx, & 9.xx it is adjustable from 4K to 32K-1. The engine is filling > >the next buffer in the background for you so it will be ready when needed. > > > >Art S. Kagel > > > >TN wrote: > >> > >> I am wondering if I write and ESQL-C application > >> that has a loop fetching through an opened cursor > >> does INET perform any caching of records so > >> that each fetch does not require a round-trip > >> cycle to the server? I am using SE 5.10 and > >> INET 5.10 on the server side. > > Andrew Lennard andy@kontron.demon.co.uk
Andy Lennard wrote: > > Out of interest, does anyone know where these buffers live? Well there are a shared pool of them in the engine in versions before 7.31, in 7.31+ each listener allocates the comm buffers it needs itself and maintains its own private memory. In your app the ESQL/CLI library will allocate and maintain a single comm buffer of the default or specified size. > I have an application that in part gets many rows from a single table > and plots the data. As an exercise to speed this up, I tried replacing > the select/fetch/etc routines with a function to return some simple > synthetic data, and was surprised to find that the application didn't > run much faster. The buffering is handled efficiently but you can speed things up by increasing the buffer size (using the env var FET_BUF_SIZE or the global variable FetBufSiz) before the first PREPARE or DECLARE in an app. You can also take advantage of the ARRAY FETCH feature to fetch the entire comm buffer's worth of data into your program space in a single FETCH which will improve speed a but more. > My conclusion was that the 'real' data was being buffered very > effectively by Informix, but was it buffered in the engine, or in my > client application? Both. Once a buffer is transmitted to the client and buffered by the library in program space the engine begins filling the next comm buffer in engine space. > Also, is it buffered in the same way for shared memory, streams and > socket connection methods? Yes, just the transmission speed and the cost of smaller buffers is effected by the connection method. Obviously the cost savings of fewer larger comm transfers is smaller for a shared memory connection than for a TCP connection over a slow WAN. Art S. Kagel > Many thanks, > Andy. > > In article <39B517F3.CEC7E665@bloomberg.net>, "Art S. Kagel" > <kagel@bloomberg.net> writes > >All Informix FETCH communications involves sending a block or rows to the > >client. In 5.xx that communications buffer size is fixed at 4K, in 6.xx, > >7.xx, 8.xx, & 9.xx it is adjustable from 4K to 32K-1. The engine is filling > >the next buffer in the background for you so it will be ready when needed. > > > >Art S. Kagel > > > >TN wrote: > >> > >> I am wondering if I write and ESQL-C application > >> that has a loop fetching through an opened cursor > >> does INET perform any caching of records so > >> that each fetch does not require a round-trip > >> cycle to the server? I am using SE 5.10 and > >> INET 5.10 on the server side. > > Andrew Lennard andy@kontron.demon.co.uk
"Art S. Kagel" wrote: > Andy Lennard wrote: > > > > Out of interest, does anyone know where these buffers live? > > Well there are a shared pool of them in the engine in versions before 7.31, > in 7.31+ each listener allocates the comm buffers it needs itself and > maintains its own private memory. In your app the ESQL/CLI library will > allocate and maintain a single comm buffer of the default or specified > size. Actually, the ESQL/CLI libraries will maintain one buffer per open cursor (otherwise it wouldn't be possible to get the benefits of buffering if more than 1 cursor was open at a time). > > I have an application that in part gets many rows from a single table > > and plots the data. As an exercise to speed this up, I tried replacing > > the select/fetch/etc routines with a function to return some simple > > synthetic data, and was surprised to find that the application didn't > > run much faster. > > The buffering is handled efficiently but you can speed things up by > increasing the buffer size (using the env var FET_BUF_SIZE or the global > variable FetBufSiz) before the first PREPARE or DECLARE in an app. You > can also take advantage of the ARRAY FETCH feature to fetch the entire > comm buffer's worth of data into your program space in a single FETCH > which will improve speed a but more. For most applications this is not necessary, but if needed the buffer size can be set independently for each cursor by changing the value of FetBufSize before a PREPARE/DECLARE is performed for a given cursor (this applies to insert cursors, too). Hope this helps, Heiko > > My conclusion was that the 'real' data was being buffered very > > effectively by Informix, but was it buffered in the engine, or in my > > client application? > > Both. Once a buffer is transmitted to the client and buffered by the > library in program space the engine begins filling the next comm buffer > in engine space. > > > Also, is it buffered in the same way for shared memory, streams and > > socket connection methods? > > Yes, just the transmission speed and the cost of smaller buffers is effected > by the connection method. Obviously the cost savings of fewer larger comm > transfers is smaller for a shared memory connection than for a TCP > connection over a slow WAN. > > Art S. Kagel > > > Many thanks, > > Andy. > > > > In article <39B517F3.CEC7E665@bloomberg.net>, "Art S. Kagel" > > <kagel@bloomberg.net> writes > > >All Informix FETCH communications involves sending a block or rows to the > > >client. In 5.xx that communications buffer size is fixed at 4K, in 6.xx, > > >7.xx, 8.xx, & 9.xx it is adjustable from 4K to 32K-1. The engine is filling > > >the next buffer in the background for you so it will be ready when needed. > > > > > >Art S. Kagel > > > > > >TN wrote: > > >> > > >> I am wondering if I write and ESQL-C application > > >> that has a loop fetching through an opened cursor > > >> does INET perform any caching of records so > > >> that each fetch does not require a round-trip > > >> cycle to the server? I am using SE 5.10 and > > >> INET 5.10 on the server side. > > > > Andrew Lennard andy@kontron.demon.co.uk
In article <p1Sr5.13795$Q36.1013083@bgtnsc07-news.ops.worldnet.att.net>, "TN" <nobody.nobody@worldnet.att.net> wrote: > I am wondering if I write and ESQL-C application > that has a loop fetching through an opened cursor > does INET perform any caching of records so > that each fetch does not require a round-trip > cycle to the server? I am using SE 5.10 and > INET 5.10 on the server side. > > Where is the documentation on all of this? I looked on inet's website, but couldn't find anything but documentation of JDBC 2.0. Sent via Deja.com http://www.deja.com/ Before you buy.
Heiko Giesselmann wrote: > > "Art S. Kagel" wrote: > > > Andy Lennard wrote: > > > > > > Out of interest, does anyone know where these buffers live? > > > > Well there are a shared pool of them in the engine in versions before 7.31, > > in 7.31+ each listener allocates the comm buffers it needs itself and > > maintains its own private memory. In your app the ESQL/CLI library will > > allocate and maintain a single comm buffer of the default or specified > > size. > > Actually, the ESQL/CLI libraries will maintain one buffer per open cursor > (otherwise it wouldn't be possible to get the benefits of buffering if more than 1 > cursor was open at a time). That is a valid point, good catch. > > > I have an application that in part gets many rows from a single table > > > and plots the data. As an exercise to speed this up, I tried replacing > > > the select/fetch/etc routines with a function to return some simple > > > synthetic data, and was surprised to find that the application didn't > > > run much faster. > > > > The buffering is handled efficiently but you can speed things up by > > increasing the buffer size (using the env var FET_BUF_SIZE or the global > > variable FetBufSiz) before the first PREPARE or DECLARE in an app. You > > can also take advantage of the ARRAY FETCH feature to fetch the entire > > comm buffer's worth of data into your program space in a single FETCH > > which will improve speed a but more. > > For most applications this is not necessary, but if needed the buffer size can be > set independently for each cursor by changing the value of FetBufSize before a > PREPARE/DECLARE is performed for a given cursor (this applies to insert cursors, > too). The original docs I got on this feature specified that it was a one-time allocation. Hmm, has that changed? Art S. Kagel > Hope this helps, Heiko > > > > My conclusion was that the 'real' data was being buffered very > > > effectively by Informix, but was it buffered in the engine, or in my > > > client application? > > > > Both. Once a buffer is transmitted to the client and buffered by the > > library in program space the engine begins filling the next comm buffer > > in engine space. > > > > > Also, is it buffered in the same way for shared memory, streams and > > > socket connection methods? > > > > Yes, just the transmission speed and the cost of smaller buffers is effected > > by the connection method. Obviously the cost savings of fewer larger comm > > transfers is smaller for a shared memory connection than for a TCP > > connection over a slow WAN. > > > > Art S. Kagel > > > > > Many thanks, > > > Andy. > > > > > > In article <39B517F3.CEC7E665@bloomberg.net>, "Art S. Kagel" > > > <kagel@bloomberg.net> writes > > > >All Informix FETCH communications involves sending a block or rows to the > > > >client. In 5.xx that communications buffer size is fixed at 4K, in 6.xx, > > > >7.xx, 8.xx, & 9.xx it is adjustable from 4K to 32K-1. The engine is filling > > > >the next buffer in the background for you so it will be ready when needed. > > > > > > > >Art S. Kagel > > > > > > > >TN wrote: > > > >> > > > >> I am wondering if I write and ESQL-C application > > > >> that has a loop fetching through an opened cursor > > > >> does INET perform any caching of records so > > > >> that each fetch does not require a round-trip > > > >> cycle to the server? I am using SE 5.10 and > > > >> INET 5.10 on the server side. > > > > > > Andrew Lennard andy@kontron.demon.co.uk