Brent
|
| Posted: 05/02/2002, 8:14 AM |
|
There is a crippling problem with the navigator and large tables. Did you know
that the Open() method counts the rows in the query using "select count(*) from table"
every time the page opens? It uses this to set up the navigator's "Page 1 of x" text.
This is EXTREMELY INEFFICIENT because at the very least you're going through the data twice. You'll
end up adding more machines to handle the same # of requests to your web site.
Even if a simple navigator is used, with just previous and next buttons,
this Count(*) is still executed even though the count value is not used.
If you have a large table, say 1 million rows, it will physically count the rows
EVERY TIME the grid page opens. On my machine it takes around 7 seconds and 100%
CPU utilization for just 1 user when it executes this "select count(*) from table"!
This is extremely time consuming and will quickly overtax the database server.
I expect the database server (MySQL) will support around 5 users before it
maxes out when using large table. This is very serious stuff.
Changing the query to "select count(*) from table where ... LIMIT 25" does no good. The
count(*) will still count all million rows from the table. (The LIMIT is not applied to
the count.) In fact, the count returned will be inaccurate. It will show 1 million rows
when in fact the query will have returned a maximum of 25 rows.
Solution: Delay the counting of the rows until the query has opened! When the query
is executed, MySQL (and other databases) can return the # of rows in the query
(mysql_num_rows function). This solution is extremely fast and doesn't put an
additional burden on the database server by going through the records twice. If
the user is using "LIMIT n" on his query, it will also return the correct count
and the navigator text will be accurate.
This solution will allow CCS to use a Navigator with large tables. It will also
help to speed up your web page on smaller tables because it no longer has to
physically count the rows as a separate step. This means you'll be able to process
more web pages / second with the equipment you have.
Hopefully CC will look into this.
|
|
|
 |
Jeremy
|
| Posted: 05/02/2002, 9:05 AM |
|
I was curious about slow downs on some of my grids, and I wondered why it was slowing....this may be why.
Thanks.
|
|
|
|
Brent
|
| Posted: 05/02/2002, 9:53 AM |
|
The speed delay is most evident if you have a large query because the Limit
clause is ignored when counting. If you don't have a large query (< 5k rows)
then the extra delay won't show up during testing. (But the delay will show up
if you have a hundred people accessing your webserver because 100 x 0.25 seconds
does add up!) If you think about it, if your website is displaying 100 grid pages
per second, executing another 100 count queries/second is going to slow it
down quite a bit. But I'm confident CC can optimize this. If not we can always
drop the navigator or rewrite it.
But another message I posted last week (I can't find the original message) was about
CCS grids displaying twice as slow as CC. In my benchmarks, CC grids
would display in 2.5 seconds and the same grid takes 5.25 seconds with CCS.
Now this is the same table from the same database and the same columns are being displayed.
These tables are quite small, maybe 6 records in it so the extra Count(*) won't
matter. I suspect there is more overhead in defining the classes for the form
and that's what is slowing it down. If you have a debugger you can probably spot
the bottleneck by profiling the code. I'm hoping CC can optimize this code too.
I'm spoiled by having very fast CC grids and I don't want to lose that edge with CCS. 
|
|
|
|
|
