56 million records search

Hey folks...

So I have a table that looks like this:

CREATE TABLE [tblStation] (
[CAMPAIGN] [varchar] (8),[LISTNUM] [varchar] (10),
[PHONE] [varchar] (10),
[EVENTTIME] [datetime] ,
[STATION] [int],
[OPERATOR] [varchar] (16),
[EVENTCODE] [varchar],
[CALLSPAN] [decimal](18, 0),
[FDISP] [int],
[RECORDNUM] [varchar],
[STC] [varchar],
[PROMOC] [varchar],
[EXP_CAMP] [varchar],
[PROMO3] [varchar],
[MAXATT] [char],[LISTNAME] [varchar],
[SITENAME] [char],
[Row_id] [int] IDENTITY

It's taking nine seconds to run the following command:

SELECT count([fdisp])

FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)

WHERE fdisp IS NULL

Anyone familiar with a table of this size having performance like
this? The [fdisp] column has a non clustered index on it.

Thanks in advance...> SELECT count([fdisp])
> FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)
> WHERE fdisp IS NULL

First of all, this query doesn't make any sense. The expression
COUNT([FDISP]) will only count FDISP values that are not null and you
have also specified WHERE FDISP IS NULL. Consequently, the result will
always be zero. For the purpose of discussion, I'll assume you meant to
specify COUNT(*):

SELECT COUNT(*)
FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)
WHERE FDISP IS NULL

Do you also have a clustered index on the table? About how many rows
have a NULL FDISP value? 9 seconds may be reasonable for an index
seek/scan depending on the amount of data that needs to be read to
determine the count.

--
Hope this helps.

Dan Guzman
SQL Server MVP

--------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index...epartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--------

"Jesus Christ's Evil Twin" <radpin@.hotmail.com> wrote in message
news:2a0f8137.0311041354.5da87b63@.posting.google.c om...
> Hey folks...
> So I have a table that looks like this:
> CREATE TABLE [tblStation] (
> [CAMPAIGN] [varchar] (8),
>[LISTNUM] [varchar] (10),
> [PHONE] [varchar] (10),
> [EVENTTIME] [datetime] ,
> [STATION] [int],
> [OPERATOR] [varchar] (16),
> [EVENTCODE] [varchar],
> [CALLSPAN] [decimal](18, 0),
> [FDISP] [int],
> [RECORDNUM] [varchar],
> [STC] [varchar],
> [PROMOC] [varchar],
> [EXP_CAMP] [varchar],
> [PROMO3] [varchar],
> [MAXATT] [char],
>[LISTNAME] [varchar],
> [SITENAME] [char],
> [Row_id] [int] IDENTITY
> It's taking nine seconds to run the following command:
> SELECT count([fdisp])
> FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)
> WHERE fdisp IS NULL
>
> Anyone familiar with a table of this size having performance like
> this? The [fdisp] column has a non clustered index on it.
> Thanks in advance...