Dharma Mulya
|
| Posted: 12/13/2002, 5:16 AM |
|
I have a same problem when working with large table. Is the a solution to
this as I am not clear with Brent explanation.
>Brent Wrote
Date Created 5/2/2002 8:14:11 AM
Message 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.
Thank you again everyone.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
|
|
|
 |
jc
|
| Posted: 12/13/2002, 8:21 AM |
|
Has your table the primary key?
Dharma Mulya wrote:
> I have a same problem when working with large table. Is the a solution to
> this as I am not clear with Brent explanation.
>
>
>>Brent Wrote
>
> Date Created 5/2/2002 8:14:11 AM
> Message 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.
>
> Thank you again everyone.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
>
>
|
|
|
|
DonB
|
| Posted: 12/13/2002, 7:32 PM |
|
A smart database engine will not read the rows for a count(*), but instead
will maintain this value in its metadata, so that it requires very little
overhead. This will, of course, require a properly-indexed table. One of
these two issues is tripping you up. I assume mySQL is smart enough to just
lookup the count instead of doing a scan.
DonB
"jc" <nomail@nomail.com> wrote in message
news:atd1ec$q66$1@news.codecharge.com...
> Has your table the primary key?
>
> Dharma Mulya wrote:
> > I have a same problem when working with large table. Is the a solution
to
> > this as I am not clear with Brent explanation.
> >
> >
> >>Brent Wrote
> >
> > Date Created 5/2/2002 8:14:11 AM
> > Message 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.
> >
> > Thank you again everyone.
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
> >
> >
>
|
|
|
|
Dharma Mulya
|
| Posted: 12/15/2002, 6:48 PM |
|
yes...?
"jc" <nomail@nomail.com> wrote in message
news:atd1ec$q66$1@news.codecharge.com...
> Has your table the primary key?
>
> Dharma Mulya wrote:
> > I have a same problem when working with large table. Is the a solution
to
> > this as I am not clear with Brent explanation.
> >
> >
> >>Brent Wrote
> >
> > Date Created 5/2/2002 8:14:11 AM
> > Message 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.
> >
> > Thank you again everyone.
> >
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
> >
> >
>
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
|
|
|
|
RonB
|
| Posted: 12/19/2002, 1:46 AM |
|
I your using mysql 4.0 make sure you activated the query cache option. A lot
of tables dont get updated much so are excelent candidates for the query
cache. The count(*) would very probably be fetched from the cache and
require almost no extra time or resources.
RonB
"Dharma Mulya" <dharma@supreme.com.my> schreef in bericht
news:atcmj5$5l8$1@news.codecharge.com...
> I have a same problem when working with large table. Is the a solution to
> this as I am not clear with Brent explanation.
>
> >Brent Wrote
> Date Created 5/2/2002 8:14:11 AM
> Message 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.
>
> Thank you again everyone.
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.427 / Virus Database: 240 - Release Date: 12/6/02
>
>
|
|
|
|
|
