Subject Re: [firebird-support] Re: FB 1.5 final hangs with this exists statement
Author James
Hi

Svein Erling wrote:

> > Iam sorry for my early post, I have make a mistake. Executing the
> > second sql statement doesn't make my FB hang, but it takes too long
> > about 3 mins I think.Why is it there is a lot if speed difference
> > between the '>' and the '<'?
>
> This one is simple to answer, '>' can use an ascending index, whereas
> '<' can use a descending index.

You mean I have to index the "Grand Total" field twice, one with
ascending and the other with descending?

>
> > For the index. I have the DRID which is my primary key which has
> > statistic of 0.00009149131 and the "Order Date" field which has
> > statistic of 0.00284090917. Iam not familiar with the plan but the
> > adopted plan is plan (D natural) plan (M natural)
>
> You do not want plan (D natural) plan (M natural), you want to use an
> index where the field "Cust ID" is the first field of the index.
> Either such an index does not exist or it is disabled.
>
> Create that index and prepare yourself for quick execution!

I have play around with putting some index. and the first thing I said
is WOW. My question is putting an index in custid makes a lot of
difference in the performance while putting an index in grandtotal
dosn't affect after all. I don't get it why since the Grand Total field
is also involve in the where clause...

Cheers,
James