buppcpp wrote:

>When I group by the primary key, GROUP BY do not use the index.
>
>And the query takes an extremely long time!
>
>I really think there are some problems with GROUP BY and DISTINCT
>guys.
>
>

Guy,

I think I didn't get it...

If you group by a unique field, won't you get the same as not grouping
by ???

I did a simples test

select count(*), empresaid from empresa group by empresaid

the plan was:
PLAN (EMPRESA ORDER PK_EMPRESA)
EmpresaId is the PK Field and PK_Empresa is the PK index.

it uses the index, but I don't understand what you are trying gouping by
the PK... In your query are you joinning with another table ? If it's
the case I expect to use the FK index.

like this:
select
count(*), p.ProdutoID
from
MovimentoEstoque ME
join Lote L on (L.LoteID = ME.LoteID)
join Produto P on (P.ProdutoID = L.ProdutoID)
group by
p.ProdutoID

the plan was:
PLAN JOIN (P ORDER PK_PRODUTO,L INDEX (FK_LOTE_PRODUTO),ME INDEX
(FK_MOVIMENTOESTOQUE_LOTE))

the query take less than a second to prepare, execute and fetch all
Produto = 888 records
Lote = 10k records
MovimentoEstoque = 130k records

I know you are talking about millions records... but

Could you show your query ?

what more info could you provide ? (table structure, index statistics,
database statistics, cache size, page size, etc.) Maybe something here
could enlighten somebody to shed a light on your case.

Distinct needs a sort, as told earlier, in general a sort is performed
faster than an indexed scan (to get the entire result set, but slower to
get the first row), it is better to find all records in natural order
(for example) and sort it then in memory then go the index page read it
and than read a data page, then go and read the index page again and
then read the same data page again.

see you !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.3.2 - Release Date: 31/05/2005