Speed up the process of count() 
Author Message
 Speed up the process of count()

I'm designing a course arrangement system for my school. There
will be about 6000 students to access the system during 1 week
and I guess the heaviest work load will happen when 100 students to
do course adding/dropping in 1 minute. Generally speaking, to perform
100 adding/dropping tasks is not a big deal for my system, but
the process of "determining how many students have been in a course"
will degrade my system performance. I'm just using count() to
caculate. Is there any way to speed it up, or the other way
to work around ?
---
Best Regards,

Kuo-Chien Kai
----------------------------------------------------------------

National Tsing Hua University   |  Phone: +886-35-715131-1123
Hsinchu, Taiwan 30043           |    Fax: +886-35-710776



Thu, 09 Dec 1999 03:00:00 GMT
 Speed up the process of count()

This is a multi-part message in MIME format.

------=_NextPart_000_01BC8080.5B6EF9A0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Quote:
>"determining how many students have been in a course"<

Can you be a little less hypothetical?

Do you mean something like:

select count(*)
from course_enrollment
where course_id = 999

Where course_enrollment is something like:

create table course_enrollment (
course_id    int,
student_id    int)

------=_NextPart_000_01BC8080.5B6EF9A0
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head></head><BODY bgcolor=3D"#FFFFFF"><p><font size=3D2 =
color=3D"#000000" face=3D"Arial">&gt;<font size=3D2>&quot;determining =
how many students have been in a course&quot;<font =
size=3D2>&lt;<br><br>Can you be a little less hypothetical?<br><br>Do =
you mean something like:<br><br>select count(*)<br>from =
course_enrollment<br>where course_id =3D 999<br><br>Where =
course_enrollment is something like:<br><br>create table =
course_enrollment (<br>course_id &nbsp;&nbsp;&nbsp;int,<br>student_id =
&nbsp;&nbsp;&nbsp;int)<br><br></p>
</font></font></font></body></html>
------=_NextPart_000_01BC8080.5B6EF9A0--



Sat, 11 Dec 1999 03:00:00 GMT
 Speed up the process of count()

Quote:

> I'm designing a course arrangement system for my school. There
> will be about 6000 students to access the system during 1 week
> and I guess the heaviest work load will happen when 100 students to
> do course adding/dropping in 1 minute. Generally speaking, to perform
> 100 adding/dropping tasks is not a big deal for my system, but
> the process of "determining how many students have been in a course"
> will degrade my system performance. I'm just using count() to
> caculate. Is there any way to speed it up, or the other way
> to work around ?
> ---
> Best Regards,

> Kuo-Chien Kai
> ----------------------------------------------------------------

> National Tsing Hua University   |  Phone: +886-35-715131-1123
> Hsinchu, Taiwan 30043           |    Fax: +886-35-710776

Hi,

Take a look inside sp_spaceused procedure
Hope this help.

Regards,
Haroldo



Sat, 11 Dec 1999 03:00:00 GMT
 Speed up the process of count()

Quote:

> will degrade my system performance. I'm just using count() to
> caculate. Is there any way to speed it up, or the other way
> to work around ?

Try to create a non-clustered index on a unique field in your table (say
student ID)and then select count(student_id) from <your table>.

This technique is called index covering..

Hope this help

Ken
UNIX/RDBMS Administrator
Hong Kong Aircraft Engineering Co. Ltd.



Sun, 12 Dec 1999 03:00:00 GMT
 Speed up the process of count()

Haroldo, Kuo-Chien,

You can use a covered-index to handle a count(*) rather than a
full table-scan.  The way to do it is to pick the smallest (or one
of the smallest)columns in the table, and create a single-column,
non-unique clustered index on it.  Then, when you do the count(*)
for the table, the SQL Server optimizer first looks to see what the
smallest non-clustered index is that can be used and it if can
use it with fewer I/Os than a table scan, then it will.

Also, 16KB I/Os can help such an index scan in System 11 and above.
A small 16KB I/O pool would help such a count.

And, as questioned "... will degrade my system performance..." any
query at all degrades performance - you just want to make sure
you write good queries.  The fastest system is one in which no
user is accessing it.  Any work thrown at a system should be known
for its resource requirements.  (this is the Zen of computing...)
Tune accordingly, and know the system capabilities when writing
queries.

Oh yeah - and sp_sysmon would be better for a good idea of what
the table has as far as rowcount, but you can also use the following:

SELECT rowcnt(doampg) from sysindexes
where id = object_id("your_tablename") and indid in (0,1)

There are times when this function is not exactly correct and because
of that, you can get actual results with a count().  The times
that it may not be correct is following a hard-crash of SQL Server.
Using a DBCC CHECKTABLE() the rowcnt in the doampg will be reset,
if it is incorrect.

Quote:


> > I'm designing a course arrangement system for my school. There
> > will be about 6000 students to access the system during 1 week
> > and I guess the heaviest work load will happen when 100 students to
> > do course adding/dropping in 1 minute. Generally speaking, to perform
> > 100 adding/dropping tasks is not a big deal for my system, but
> > the process of "determining how many students have been in a course"
> > will degrade my system performance. I'm just using count() to
> > caculate. Is there any way to speed it up, or the other way
> > to work around ?
> > ---
> > Best Regards,

> > Kuo-Chien Kai
> > ----------------------------------------------------------------

> > National Tsing Hua University   |  Phone: +886-35-715131-1123
> > Hsinchu, Taiwan 30043           |    Fax: +886-35-710776

> Hi,

> Take a look inside sp_spaceused procedure
> Hope this help.

> Regards,
> Haroldo

--
John McVicker
Principal Consultant, District Lead Architect
Sybase Professional Services
Pennsylvania/New Jersey District
301-896-1765


http://www.sybase.com
http://www.powersoft.com
http://www.isug.com



Tue, 14 Dec 1999 03:00:00 GMT
 Speed up the process of count()

Quote:

> I'm designing a course arrangement system for my school. There
> will be about 6000 students to access the system during 1 week
> and I guess the heaviest work load will happen when 100 students to
> do course adding/dropping in 1 minute. Generally speaking, to perform
> 100 adding/dropping tasks is not a big deal for my system, but
> the process of "determining how many students have been in a course"
> will degrade my system performance. I'm just using count() to
> caculate. Is there any way to speed it up, or the other way
> to work around ?

Are you planning to use any nonclustered indexes? select count(*) will
use the smallest nonclustered index available on the table, and do a
covered index scan to count the rows. So if the course id is the first
(or only) key in an index, a query like this:

select course_id, count(*) from
enrollment_tpl
where course_id = "X"
group by course_id

will use the index to 'position the scan' at the first matching row,
and scan forward on the index pages and count rows until the keys no
longer match. Scanning on the index pages vastly reduces the number
of I/Os needed -- the smaller the key, the better the performance.

The downside: maintaining the index take extra work, you may see more
blocking if multiple inserters are working on the same index key.



Wed, 15 Dec 1999 03:00:00 GMT
 Speed up the process of count()

Karen,

Thanks for your suggestion.
---
Best Regards,

Kuo-Chien Kai
----------------------------------------------------------------

National Tsing Hua University   |  Phone: +886-35-715131-1123
Hsinchu, Taiwan 30043           |    Fax: +886-35-710776



Thu, 16 Dec 1999 03:00:00 GMT
 
 [ 7 post ] 

 Relevant Pages 

1. NT speeds-ups?

2. Speed, speed, speed, and Sybase disk limitations

3. Speed, Speed, Speed, and a ramdisk too

4. Speed, speed, speed...

5. Speeding record count procedure

6. How to speed up count(*) on group by ?

7. Sybase SQL Anywhere 5.0.02 - count(*) - speed ??

8. Cube processing speed

9. cube size and processing speed.

10. How to increase the speed of process bulk data

11. Processing Speed Reading From SQL vs. Oracle

12. DRILLDOWNLEVEL PROCESSING SPEED and EXECUTION PLAN


 
Powered by phpBB® Forum Software