SQL Server 2000 and a basic select count * issue

Last Post 06 Nov 2009 07:49 AM by Markus_SQL. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sumerian
New Member
New Member

--
06 Nov 2009 06:19 AM
Hi there - have a weird thing happening on one of my tables in a legacy sql 2000 database. The table ion question has around 6.4million rows and has a primary key index on it. The problem is that if you issue

Select count * from mytable

the query just runs and runs. The table is just a staging table and as such gets truncated every so often when new data arrives for processing.

If you then run the
Select top 1000 * from mytable and slowly increase the value it does return results - however certainly not as quick as we'd expect. If you have a look at the actual execution plan for each of the incremental counts we run it is using the PK index on the table to obtain the info.

We even tried to drop the index but it said it could not be done due to the fact the index was tied to another table. But this was not the case - totally weird.

Has anyone got any ideas what might be causing this?

Thanks in advance
Markus_SQL
New Member
New Member

--
06 Nov 2009 07:49 AM
Try running select count(1) from ......... runs MUCH quicker
You are not authorized to post a reply.

Acceptable Use Policy