Subject Re: Problem slow sorting/distinct in Firebird
Author vanderlinde81
Hello,

I have the order of the fields modified:

Prepare time: 0.005s
Field #01: TREE3.NODEPOSITION Alias:NODEPOSITION Type:INTEGER
Field #02: TREE3.IDLINK Alias:IDLINK Type:INTEGER
Field #03: TREE3.IDTARGETLINK Alias:IDTARGETLINK Type:INTEGER
Field #04: TREE3.IDITEM Alias:IDITEM Type:INTEGER
PLAN SORT (JOIN (TR INDEX (IDX_TREE3_2, IDX_TREE3_3), ART INDEX (RDB$PRIMARY2)))


873424 fetches, 0 marks, 12892 reads, 0 writes.
0 inserts, 0 updates, 0 deletes, 380580 index, 0 seq.
Delta memory: -8 bytes.
Total execution time: 1.538s

My settings are:

ODS Version= 11.2
Page size= 16384
Pages= 5247
Size on disk= 81.98MB
Page buffers= 75
Read only= false
Dialect= 3
Default character set= NONE
Sweep interval= 20000


Thanks!

An VanderLinde


--- In firebird-support@yahoogroups.com, Dmitry Yemanov <dimitr@...> wrote:
>
> 15.11.2012 0:16, vanderlinde81 wrote:
> >
> > The Query in FireBird:
> >
> > SELECT FIRST 30 SKIP 0 distinct tr.IdLink, tr.IdTargetLink, tr.IdItem,
> > tr.NodePosition
>
> What about reordering the fields as
>
> SELECT FIRST 30 SKIP 0 distinct tr.NodePosition, tr.IdLink,
> tr.IdTargetLink, tr.IdItem
>
> I'd expect PLAN to change to
>
> > PLAN SORT (JOIN (TR INDEX (IDX_TREE3_2, IDX_TREE3_3), ART INDEX
> > (RDB$PRIMARY2)))
>
> that would eliminate one of the sorts.
>
> > 873424 fetches, 0 marks, 12892 reads, 0 writes.
>
> What is your page cache setting?
>
>
> Dmitry
>