Re: Very slow distinct select
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Tue, 26 Jul 2005 08:59:48 +0000 (UTC)
Rich (no@xxxxxxxxxxxx) writes:
> 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?
Assuming that these columns are not really of fixed length, there is
quite something to win by changing char to varchar. char is fixed
length. Your row size is 854 bytes, which means that you can get at
most 9 rows per page. Thus your table requires 32628 pages @ 8192
bytes for a total of 267 MB. Which is a small table.
Say that the average length of all columns is 40 bytes. Then your average
row size is 5*(40+2) + 4 =214, giving 37 rows per page. This reduces the
size of the table to 65 MB.
The smaller the pages, the fewer pages to read, and thus the shorter the
response time.
Adding a non-clustered index as Dan suggested is of course even better
because then the table you traverse is only the index. And if you
use varchar instead that index is even smaller.
Note that I and Dan assume that your query really is
SELECT COUNT(DISTINCT REMOTE_ADDR) FROM tbl
and nothing else. If there is a WHERE clause involved that refers to
other columns, then the index is not that useful.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
.
- References:
- Very slow distinct select
- From: Rich
- Very slow distinct select
- Prev by Date: Re: Very slow distinct select
- Next by Date: Re: Use SQL-DMO, ADO or ADO.NET?
- Previous by thread: Re: Very slow distinct select
- Next by thread: Re: Very slow distinct select
- Index(es):
Relevant Pages
|
Loading
