Correlated Subqueries vs Foreach Loop (was: Restricting queries to a certain number of rows?)
In article <o3IW+KACVHyxEw60@smooth1.demon.co.uk>, David Williams writes:
>
> In article <4q7et2$15q@cssun.mathcs.emory.edu>, Spetc@aol.com writes
> >Does anybody know if it is possible to restrict the number of rows returned
> >by the Online engine (Version ?.?).
> >
> >Clearly rows past say, 100, can be discarded in the application, but I would
> >prefer to tell the engine not to return more than 100.
>
> No it is not possible - OnLine always returns all the rows from a
> query,
> --
> David Williams
However, one trick exists that allows to avoid using of counters, though
it's almost always not applicable because it takes forever. The solution
is called "Correlated Subquery" and described in INFORMIX "Guide to SQL,
Tutorial" on page 3-33.
In short, the following query will return exactly 10 latest records from
table "some_table" (if some_table contains more than 10 records, obviously)
Say, some_table = ( serial_id serial, ..... ) then
select *
from some_table a
where 10 >
(select count(*)
from some_table b
where b.serial_id > a.serial_id);
Here are experimental results i got on my old 486-based SCO UNIX box
and the test database (OnLine v.5.01):
+============+=======================+=======================+
| Number | correlated subqueries | foreach loop |
| of +-------+-------+-------+-------+-------+-------+
| rows | real | user | sys | real | user | sys |
+============+=======+=======+=======+=======+=======+=======+
| 57 | 0.8| 0.5| 0.1| 0.2| 0.0| 0.0|
| 2690 |18:44.4|18:06.7| 5.3| 0.2| 0.0| 0.0|
| 6559 |3:47:12|3:25:14| 1:12.2| 0.3| 0.0| 0.0|
| 59456 | ??? | ??? | ??? | 0.9| 0.0| 0.0|
+============+=======+=======+=======+=======+=======+=======+
Take care,
Pavel. <pavel@cso.spb.su>