[thesite] friday is a good day..

Joshua OIson joshua at alphashop.net
Fri May 18 14:59:39 CDT 2001


Jeff,

you wrote,

> :~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : does oracle support anything like microsoft's
> : SELECT TOP or mysql's LIMIT?
> :~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> good question, but that won't help us out in this situation as we need to
> generate the entire resultset in order to page through since coldfusion
> needs to have the entire resultset for startrow and maxrows attributes of
> the cfoutput to work properly.  the only exception is if we can pass to
the
> query where in its internal resultset we want to start and how many rows.
> even if this were possible, which i don't think it is, i don't see this
> improving the time of the query as it now not only has to retrieve the
> entire resultset internally, it then has to grab the portion of the data
> that meets the startrow and maxrows criteria.

I disagree.  You say that we need to grab the entire recordset to generate
the page throughs?  I say not to that.  To generate the page throughs all we
need is to:

1) know how many total records we are dealing with
2) determine which of the database records fall within the current "page" we
are viewing

The first piece of information can be done with a COUNT and only needs to be
recalculated when an article is added, thus disrupting the count.  So,
figure out what that number is and store it somewhere.

The second piece is a hashing algorithm of some sort.  You can make a
counter that ONLY counts the articles.  1 is the oldest article, max is the
newest.  That way, all you do is make the newest article the next number.
Use this number in conjunction with the number we already know (how many
articles there are total) which, btw, is the same number, to figure out
which subset to query.  Then you can put the constraints into the query
without worrying about limits etc.  Just say I want article max_id through
max_id-10!  Where max_id is the id of the newest article.

Shouldn't be too tough.  Waddaya think?

-joshua





More information about the thesite mailing list