Retrieving query results Row-by-Row.

An Informix server has to collect a query's entire result set and deliver it to the client as a single result before any subsequent scroll, update, delete, etc. can be performed. This can be unacceptable for queries that return a large number of rows or take a long time to only return few rows. I would like to know if a directive, other than FIRST ROWS, can be implemented so that the result row(s) can be returned to the client one at a time, as they are received from the server? This row-by-row mode should only be effective for the currently query being executed. PostgreSQL 9.2.4 has this feature and I must say it functions very well! See: http://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html

Actually, if there is no sorting involved, Informix only collects enough rows to fill the current communications buffer (FET_BUF_SIZE) before returning that block to the client. The minimum and default buffer is 4K, but you can increase it, which I understand is not where you are going. Also, if there is any sorting required, then obviously the entire result set must be gathered before sorting can complete and data can be returned. What you may want to look at is FIRST_ROWS optimization which tells the engine that you want it to optimizer for the shortest time to return that first block of data. That will cause the optimizer to favor indexes over sorting to satisfy GROUP BY and ORDER BY requirements. When the option first became available I had a query that returned over 15000 rows total, and took only 14.9 seconds to return all that data to the application. Unfortunately we needed to present the first 20 rows on screen for users in under 3 seconds and the first buffer full of data wasn't arriving in the application's memory space for 13.5 seconds. With FIRST_ROWS optimization enabled the optimizer chose to use an existing index instead of sorting. The first rows arrives in about 1.8 seconds and although the full 15,000 row result set now took over 16 seconds to arrive, we didn't care because it would take the users far more than 16 seconds to decide that they needed to see that 750th screenful of data! The optimizer was doing the correct thing by sorting if its goal was the default goal of ALL_ROWS optimization. Anyway, you can get FIRST_ROWS with an ONCONFIG parameter for the whole server, an environment variable for an entire session, or an optimizer directive for a single query. Art Art S. Kagel Advanced DataTools (www.advancedatatools.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 my employer, Advanced DataTools, 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 Mon, Aug 12, 2013 at 7:34 PM, FRANCISCO USER/DEVELOPER < frankcomputer@ymail.com> wrote: > An Informix server has to collect a query's entire result set and deliver > it > to the client as a single result before any subsequent scroll, update, > delete, > etc. can be performed. This can be unacceptable for queries that return a > large number of rows or take a long time to only return few rows. I would > like > to know if a directive, other than FIRST ROWS, can be implemented so that > the > result row(s) can be returned to the client one at a time, as they are > received from the server? > > This row-by-row mode should only be effective for the currently query being > executed. PostgreSQL 9.2.4 has this feature and I must say it functions > very > well! > > See: http://www.postgresql.org/docs/9.2/static/libpq-single-row-mode.html > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e0160b5e40433a704e3c945d9