General question on queries.

[brianpercival]

This might apply to any platform, but what is the best way to implement the "page numbers"?

meaning,

I run a query and then display the results say 10 per page, with provision to go to nth page etc etc.

in cf, we use startrow,endrow somehow and we browse.

But for all the pages, we are running the query again and again. IS there anyway, we can avoid doing that?

Any good articles on this?

I hope u got what I am asking for..
The motivation behind this being, I have got a table with almost half a million records and I need to do searches on the table. Each time I go to next page is taking like ages.


regards,
Brian

 

[TruthInSatire]

in cf you can cache the query so it pulls it from memory vice the db.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.

 

[brianpercival]

Caching a ver huge result set (like say thousands of records) might be a performance hit. But is there no generic pattern for this kind of problem? No workaround for executing same query multiple times just to get a chunk of the resultset?

regards,
brian

 

[TruthInSatire]

Your solution is most likely going to be found in the DB then. Consider views, or daily updated temp tables.

We've heard that a million monkeys at a million keyboards could produce the complete works of Shakespeare; now, thanks to the Internet, we know that is not true.