Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

count(*) is so slow?

4 posts in Windows NT Last posting was on 2000-11-16 23:14:52.0Z
Mathew Chacko Posted on 2000-11-16 16:04:06.0Z
Message-ID: <3A140576.6D277FC8@hotmail.com>
Date: Thu, 16 Nov 2000 17:04:06 +0100
From: Mathew Chacko <mathew_chacko@hotmail.com>
X-Mailer: Mozilla 4.75 [en] (WinNT; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: count(*) is so slow?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 6
NNTP-Posting-Host: gk-red.unicc.org 192.91.247.2
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1483
Article PK: 1088961

Environment: Sybase Adaptive Server 11.9.2 on Windows NT 4.0

Question: Why 'Select count(*) from tablename', take 3.5 - 4 min. for
6,761,538 rows?
Can I improve the performance?
Thank you in advance.


Rob Verschoor Posted on 2000-11-16 23:14:52.0Z
Reply-To: "Rob Verschoor" <rob@sypron.nl>
From: "Rob Verschoor" <rob@sypron.nl>
References: <3A140576.6D277FC8@hotmail.com>
Subject: Re: count(*) is so slow?
Date: Fri, 17 Nov 2000 00:14:52 +0100
Lines: 31
Organization: Sypron B.V.
X-Newsreader: Microsoft Outlook Express 5.00.2919.6600
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600
Message-ID: <rdme#UCUAHA.269@forums.sybase.com>
Newsgroups: sybase.public.sqlserver.nt
NNTP-Posting-Host: i0754.pvu.euronet.nl 194.134.166.244
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1478
Article PK: 1088955


"Mathew Chacko" <mathew_chacko@hotmail.com> wrote in message
news:3A140576.6D277FC8@hotmail.com...
> Environment: Sybase Adaptive Server 11.9.2 on Windows NT 4.0
>
> Question: Why 'Select count(*) from tablename', take 3.5 - 4 min.
for
> 6,761,538 rows?
> Can I improve the performance?
> Thank you in advance.
>

Run "sp_spaceused your_table_name" instead. This is very fast and does
not scan the entire table, as select count(*) does.

HTH,

Rob
----------------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.0
Certified Sybase Professional DBA/SQL Developer for ASE 11.5
Certified DBA/Performance & Tuning Specialist for Sybase System 11

email mailto:rob@sypron.nl.*No*Spam*Please*
WWW http://www.sypron.nl
snail Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
----------------------------------------------------------------------


Eric Miner Posted on 2000-11-16 16:43:40.0Z
Message-ID: <3A140EBC.3F986333@sybase.com>
Date: Thu, 16 Nov 2000 08:43:40 -0800
From: Eric Miner <eminer@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Mathew Chacko <mathew_chacko@hotmail.com>
CC: eminer@sybase.com
Subject: Re: count(*) is so slow?
References: <3A140576.6D277FC8@hotmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 15
NNTP-Posting-Host: eminer-pc.sybase.com 130.214.119.202
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1481
Article PK: 1088957

Try

select count (col_name)

where col_name is the leading column of a non-clustered index. There will
be an index scan but it will be less costly than a table scan. You can
also use optdiag if you're just after the current row count and don't
need it in a query.

Hope this helps

Eric Miner
Sybase
ASE Engineering
Optimizer Group

Mathew Chacko wrote:

> Environment: Sybase Adaptive Server 11.9.2 on Windows NT 4.0
>
> Question: Why 'Select count(*) from tablename', take 3.5 - 4 min. for
> 6,761,538 rows?
> Can I improve the performance?
> Thank you in advance.


Bret Halford Posted on 2000-11-16 16:14:36.0Z
Message-ID: <3A1407EC.D8023C2F@sybase.com>
Date: Thu, 16 Nov 2000 09:14:36 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.5 [en]C-CCK-MCD (WinNT; I)
X-Accept-Language: en,ja
MIME-Version: 1.0
Subject: Re: count(*) is so slow?
References: <3A140576.6D277FC8@hotmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.sqlserver.nt
Lines: 23
NNTP-Posting-Host: bret-pc.sybase.com 130.214.51.200
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.sqlserver.nt:1482
Article PK: 1088960

count(*) has to do a table scan of the entire table. You might be able
to speed it up a little
by defragmenting the table (recreate the existing clustered index, or
create a clustered index
if you don't already have one - you may drop it immediately afterward).
You could also
install faster disk drives, or increase your data cache to the point that
all your tables stay
resident in memory.

Alternatively, you could use the rowcnt() function, which just returns
the accounting figure
from the table's OAM strucuture. It isn't necessarily perfectly
accurate, but tends to be
close and the query should return very quickly.

-bret

Mathew Chacko wrote:

> Environment: Sybase Adaptive Server 11.9.2 on Windows NT 4.0
>
> Question: Why 'Select count(*) from tablename', take 3.5 - 4 min. for
> 6,761,538 rows?
> Can I improve the performance?
> Thank you in advance.