At 06:45 PM 6/5/2001 +0000, Fred Toussi ft@... wrote:

>Suppose the last movie in the first
>list is a late night screening, the next query will exclude almost
>all the movies that the full dataset of the first query would return.

Right. So replace all of the x >= :x y >= :y with

x > :x or x = :x
and (y > :y or y = :y
and (z >= :z))

Actually, this is a very interesting discussion and one I will
take up with the Tucks Point Database Design Collaborative. On
the one hand, I don't know how to implement this efficiently.
On the other, it's obviously easy to use, meets a need, and is
implemented in other databases.

Unless I've messed up the math (again) the cost of this method
is approximately

(n * (n + g)) / 2 g

where n is the total number of rows returned and g is the size
of the group. In other words, this series:

LIMIT (1, 5) LIMIT (6, 10) LIMIT (11, 15) LIMIT (16, 20)

has an n value of 20 and a g value of 5 and results in fetching
55 rows. For relatively small values of n and large values of
g, it's not so bad. Retrieving 100 rows in groups of 10 requires
reading 5500 rows. Clearly the developer can control that. And
Firebird/InterBase developers are adults, capable of making intelligent
choices. But it bugs me.


Regards,

Ann
www.ibphoenix.com
We have answers. Sometimes.