Adam M
|
| Posted: 03/06/2002, 9:07 AM |
|
CC R&D,
When navigating pages, CC queries for a recordset, of which only a subset is required for display on a given page.
Wouldn't it be way more efficient just to query and return the 20 records that are required for a page?
|
|
|
 |
Omaha
|
| Posted: 03/06/2002, 11:54 AM |
|
yeah but how would sorting work given that the sort is based on all the records and not just those being displayed?
|
|
|
|
Nicole
|
| Posted: 03/07/2002, 1:22 AM |
|
Anyway to get the total number of records that could be returned count(*) sql should be executed.
|
|
|
|
Adam M
|
| Posted: 03/07/2002, 1:59 AM |
|
Omaha,
The query:
"SELECT * from accounts ORDER BY AccountID LIMIT 21,20"
would return 20 records for the second page. CC would simply need to change the ORDER BY field if you need to sort records differently.
|
|
|
|
Brent
|
| Posted: 03/07/2002, 7:04 AM |
|
Adam is right, CC is using inefficient SQL to retrieve the rows for the grid.
When you look at the code you'll see CC will fetch the first "page x-1" rows
of data and discard them before it displays pagex rows. So the more the user
pages through the grid, the slower it is going to be. Here is CC code it's using:
if(($iPage - 1) * $iRecordsPerPage != 0)
{
do
{
$iCounter++;
} while ($iCounter < ($iPage - 1) * $iRecordsPerPage && $db->next_record());
$next_record = $db->next_record();
}
So every time a new page is displayed, all the pervious (page x-1) records must be traversed!
Not only that, but the SQL statement will retrieve ALL ROWS from the database
even those past "page x" every time a new page is displayed. This is grossly inefficient and will tax the
database server when only a few people are browsing a large table. Imagine doing
this on a million row table? Ouch!
Using "[offset],limit" would speed things up tremendously because only 20 rows are
returned at a time rather than thousands (millions?) of rows. CC needs to have at
least a checkbox option to use "[offset,]limit" for those databases that support. If the
database doesn't have it, then use the old inefficient method as a fall back option.
|
|
|
|
Adam M
|
| Posted: 03/12/2002, 6:37 AM |
|
I've changed to topic name to include an exclamation mark because this is an issue that really needs to be addressed.
> ... only 20 rows are returned at a time rather than
> thousands (millions?) of rows. CC needs to have at least a
> checkbox option to use "[offset,]limit" for those databases
> that support. If the database doesn't have it, then use the
> old inefficient method as a fall back option.
I would venture even farther and say "dont use the old method as fallback". Rather, if you intend to do pagination, and your database does not support the [offset,limit] option you MUST use some kind of auto-numbering field. You would then modify your sWHERE definition to limit results by this field instead of the offset/limit method.
|
|
|
|
|
