Subject Re: [firebird-support] Simple SELECT statement causes long running query
Author Lucas Franzen
Christian,


Christian G├╝tter schrieb:

> Hi,
>
> running the following query against Firebird 1.50
> causes the server to compute for a very long (infinite?)
> time:
>
> SELECT MAX (T1.ID),
> MAX (T2.ID)
> FROM Table1 T1, Table2 T2
>
> The two ID Fields are PKs, both table contain approximately
> 25 K records.
>
> I have never used such a query before, so I am wondering
> if it is valid or invalid and what is causing Firebird to
> work so long.

You should have staid with never having used such a query before ;-)

What do you expect it to do???
(and what is it good for???)

You're doing a select on two different tables that have got nothing to
do with each other, but if you'll have look at the plan of statement I'm
sure you'll see that they'll be joined automatically.

And you're asking for the MAX on a pk-field - if you haven't added an
desc index on the pk field of the tables, for each record in T1 you
should have 25k reads and each of these will read 25k times on table T2
(and after that you'll have another 25K reads on T2).

So this should some up to:

25K*25K (+25K - which is almost neglectable looking at the product
before), sth. about 625M (625.000.000) reads.

If the server will do 1 million reads per second it should last 625
seconds ;-)


Luc.