Alexandre Benson Smith wrote:

>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.
>>
>>
>>
>>

Another example:

select
count(*), NF.EmpresaID
from
MovimentoEstoque ME
join NotaFiscalItem NFI on (NFI.NotaFiscalItemID = ME.DocumentoID)
join NotaFiscal NF on (NF.NotaFiscalID = NFI.NotaFiscalID)
group by
NF.EmpresaID

PLAN SORT (JOIN (NFI NATURAL,NF INDEX (PK_NOTAFISCAL),ME INDEX
(IX_MOVIMENTODOCUMENTO)))

takes a bit over 8 seconds to prepare, execute and fetch

forcing this plan:
PLAN SORT (JOIN (NF ORDER IX_NotaFiscalEmpresaID,NFI INDEX
(FK_NOTAFISCALItem_NotaFiscal),ME INDEX (IX_MOVIMENTODOCUMENTO)))
takes a bit over 6 seconds to prepare, execute and fetch

MovimentoEstoque = 130K records
NotaFiscal = 37k records
NotaFiscalItem = 108k records

* All my tests are with FB 1.5.1 *

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