Subject Re: FB 1.5 final hangs with this exists statement
Author james_027
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.

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 '<'?

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)

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