IDS Forum

Re: Retrieving query results Row-by-Row.

Posted By: Art Kagel
Date: Monday, 12 August 2013, at 8:25 p.m.

In Response To: Retrieving query results Row-by-Row. (FRANCISCO USER/DEVELOPER)

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

Messages In This Thread

• Retrieving query results Row-by-Row.
  FRANCISCO USER/DEVELOPER -- Monday, 12 August 2013, at 7:34 p.m.
  • Re: Retrieving query results Row-by-Row.
    Art Kagel -- Monday, 12 August 2013, at 8:25 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.