[OT] Paging at the database level

[Thomas Joseph <th...@kottsoftware.com>]

Hi All,

I require that my application should have the ability for paging the results obtained in a query, typically after a search.

There are many solutions like DisplayTag, that help for paging at the View level. i.e, whole of the resultset is transferred as a bean to the view, and the paging is done there.

But suppose if the database has to return farly large rows for a query (say-3000), then retrieving all the records from db, this will be costly, as I may just need the first few results..say 100 most often, then the other results are simply wasted at the cost of my time and resources required to aquire such a big result-set.

What I think an optimum strategy would be the mid of these two ends.. fetch 100 rows from db at one go, page it at say 20 rows per page, If at all the user needs to go beyond 100, fetch next 100 rows and so on.. (What should I do with the previous Results? Keep it? Discard it? How??)

How good or bad is this idea? Also, how can I page at the DB level (paging is view activity)? what I do now is pass the offset to the query to retrieve the paged data (eg.. Top in SQL Server, Limit in MYSQL). How good or bad is the paging like this at db level? Any other ideas for the same

Any good ideas/ design in this regard ..?

Thanks and Regards,
Thomas Joseph 

Kott Software Pvt. Ltd. | Vallamattom Estate | M.G.Road | Cochin, Kerala - 682015 | India 
www.kottsoftware.com 
--------------------------------------------------------------------------------
This email (including any attachments) is subject to copyright, the information in it is confidential, and it is legally privileged. Use of this email or of any information in it other than by the addressee is unauthorized and unlawful. If you are not the intended recipient of the mail, kindly notify the author of the mail and delete the same. 
--------------------------------------------------------------------------------

RE: [OT] Paging at the database level

[David Friedman <hu...@ix.netcom.com>]

Valuelist (http://valuelist.sourceforge.net) is one paging table tag library
which has supported database side paging since April/06.  There are some
notes about it on their front page.

Regards,
David

-----Original Message-----
From: Thomas Joseph [mailto:thomas.joseph@kottsoftware.com]
Sent: Friday, September 29, 2006 12:49 AM
To: Struts Users Mailing List
Subject: [OT] Paging at the database level


Hi All,

I require that my application should have the ability for paging the results
obtained in a query, typically after a search.

There are many solutions like DisplayTag, that help for paging at the View
level. i.e, whole of the resultset is transferred as a bean to the view, and
the paging is done there.

But suppose if the database has to return farly large rows for a query
(say-3000), then retrieving all the records from db, this will be costly, as
I may just need the first few results..say 100 most often, then the other
results are simply wasted at the cost of my time and resources required to
aquire such a big result-set.

What I think an optimum strategy would be the mid of these two ends.. fetch
100 rows from db at one go, page it at say 20 rows per page, If at all the
user needs to go beyond 100, fetch next 100 rows and so on.. (What should I
do with the previous Results? Keep it? Discard it? How??)

How good or bad is this idea? Also, how can I page at the DB level (paging
is view activity)? what I do now is pass the offset to the query to retrieve
the paged data (eg.. Top in SQL Server, Limit in MYSQL). How good or bad is
the paging like this at db level? Any other ideas for the same

Any good ideas/ design in this regard ..?

Thanks and Regards,
Thomas Joseph


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@struts.apache.org
For additional commands, e-mail: user-help@struts.apache.org