|
|
Posted by Dan Guzman on 07/26/05 06:07
> REMOTE_ADDR has duplicate values. So I added a numeric identity column
A non-unique index will optimize your COUNT(DISTINCT REMOTE_ADDR). No need
to add a unique-ifier identity column.
How many distinct values? If it is fairly high, a scan of many index leaf
nodes is needed and this might not perform much better than a table scan.
In that case, you can create a view with the aggregation and then create an
index on that view. For Example
CREATE VIEW Requests_RemoteAddr
WITH SCHEMABINDING AS
SELECT REMOTE_ADDR, COUNT_BIG(*) AS CoungBig
FROM dbo.Requests
GROUP BY REMOTE_ADDR
GO
CREATE UNIQUE CLUSTERED INDEX Requests_RemoteAddr_Index ON
WebLog(REMOTE_ADDR)
GO
Your query can automatically use the view index when accessing the base
table if you are using SQL 2000 Enterprise Edition. If you are using
another edition, you'll need to access the view and add the NOEXPAND hint:
SELECT COUNT(DISTINCT REMOTE_ADDR)
FROM dbo.Results_View WITH (NOEXPAND)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rich" <no@spam.invalid> wrote in message
news:IBgFe.48800$4o.29531@fed1read06...
> REMOTE_ADDR has duplicate values. So I added a numeric identity column and
> did this:
>
> CREATE UNIQUE NONCLUSTERED INDEX Requests_RemoteAddr ON Requests
> (REMOTE_ADDR, ID)
>
> But that didn't seem to help the speed any.
>
> Any other ideas?
>
> Thanks.
>
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:09gFe.2034$PC7.1092@newssvr29.news.prodigy.net...
>> You can speed up this query with a non-clustered index on REMOTE_ADDR.
>>
>> --
>> Hope this helps.
>>
>> Dan Guzman
>> SQL Server MVP
>>
>> "Rich" <no@spam.invalid> wrote in message
>> news:DbfFe.48792$4o.35562@fed1read06...
>> > My table looks like this:
>> > char(150) HTTP_REF,
>> > char(250) HTTP_USER,
>> > char(150) REMOTE_ADDR,
>> > char(150) REMOTE_HOST,
>> > char(150) URL,
>> > smalldatetime TIME_STAMP
>> >
>> > There are no indexes on this table and there are only 293,658 records
>> > total.
>> >
>> > When I do a select like this it takes forever:
>> >
>> > SELECT COUNT(DISTINCT REMOTE_ADDR)
>> >
>> > Takes 2 minutes. Is there anyway to speed that up?
>> >
>> > Thanks
>> >
>> >
>>
>>
>
>
Navigation:
[Reply to this message]
|