Hi!

Multiply the total number of rows in online_order by 2.6 and you'll get
the maximum number of seconds the second select could take (it will be
less than this, the point is to point out that the subselect may have to
be executed for each row in the table, something that can be a very time
consuming process). Moreover, you're not using the 'count' for anything
other than finding if there's more than one match - normally it is
quicker to use EXISTS for this purpose.

If I were you, I'd rewrite your statement to something like this (there
might be syntax errors that you have to fix, I don't normally use
Firebird 2.1 myself):

with temp_order(order_id) as
(select distinct k.order_id from online_order k
where k.order_date > 01.01.2009'
and k.customer = 16244
and exists(select null from online_order k2
where k2.order_date > 01.01.2009'
and k2.customer = 16244
and k2.rdb$db_key > k.rdb$db_key))
select x.* from online_order x
join temp_order t on x.order_id = t.order_id
order by x.order_id

(I used rdb$db_key because I do not know whether you have a primary key
field in your table)

Please report back how long this statement takes.

HTH,
Set

intellekta wrote:

> Hello,
> my first --1-- select takes 2,6 seconds an the result has 51 records.
> i stop the second --2-- select after 25 minutes with no result.
> Any suggestions about this?
> Thanks in advance
>
> --1--
> select k.order_id from online_order k where k.order_date > 01.01.2009'
> and k.customer = 16244 group by 1 having count(*) > 1
>
> ------ Plan info ------
> PLAN (K ORDER ONLINE_ORDER_ORDERID INDEX (ONLINE_ORDER_CUSTOMER))
> Adapted plan:
> PLAN (K ORDER ONLINE_ORDER_ORDERID INDEX (ONLINE_ORDER_CUSTOMER))
> ------ Performance info ------
>
>
> --2-- the second with subselect is:
> select x.* from online_order x where x.order_id in
> (
> select k.order_id from online_order k where k.order_date > 01.01.2009'
> and k.customer = 16244 group by 1 having count(*) > 1
> )
> order by x.order_id
>
> ------ Plan info ------
> PLAN (K ORDER ONLINE_ORDER_ORDERID INDEX (ONLINE_ORDER_ORDERID,
> ONLINE_ORDER_CUSTOMER))
> PLAN (X ORDER ONLINE_ORDER_ORDERID)
> Adapted plan:
> PLAN (K ORDER ONLINE_ORDER_ORDERID INDEX (ONLINE_ORDER_ORDERID,
> ONLINE_ORDER_CUSTOMER)) PLAN (X ORDER ONLINE_ORDER_ORDERID)