--- In firebird-support@yahoogroups.com, "james_027" wrote:

> Hi Svein Erling
>
> > Hi James, I like your query, but in order to help you it would be
> > benefitial if you mentioned the plan chosen and some statistics. I
> > suspect you need to modify your indexes a bit or change to
> >
> > select "Cust ID", "Deliver Date", "Grand Total"
> > from "DR" m
> > where m."Cust ID" = 205 and not exists (select *
> > from "DR" d
> > where d."Cust ID" = 205 and
> > (m."Grand Total" > d."Grand Total" or 2=0))
> >
> > Though it all depends on lots of things, e.g. how many records
> > exists for "Cust ID" 205.
> >
> The total records in the "DR" table is about 11320 and the total
> records for the "Cust ID" = 205 is 162.

Huh, speed problems with a table containing only 11000 records? Be
glad you didn't try this query on a table with millions of records.

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

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

> Thanks again. I know I'll be learning a lot from this one.

That's great, James.

Set