Paul Schmidt wrote:

> Hiya List:
>
> I have a problem, large table, approx 175 fields, around 190,000
> records, I did the following SQL:
>
> SELECT REC_NB FROM TABLE1
>
> REC_NB is the primary key
>
> the plan is TABLE1 NATURAL
>

[snipped]

> It took over 3.5 minutes to return the first record!

Are you sure about this? Can you repeat it consistently? I could
understand 3.5min to get all the rows back, but not the first. The first
row should be more or less instantaneous. What sort of tool are you
using to measure this? Does it allow you to fetch rows batch by batch,
or does it dutifully pull all the rows back as requested and then
present the first n rows in a grid?

> Is there any way
> to get extra umph out of this....

Yes - put a restriction in the Where clause.

What kind of application needs 190,000 keyfields in one go?

> I tried using an order by on the
> primary key, which simply exchanged execution time for prepare
> time.....
>

Order by is not recommended unless you really need the data ordered. It
will do an on-disk sort of the data prior to returning anything.
Typically you could guestimate that the select will take twice as long.


Paul
--

Paul Reeves
http://www.ibphoenix.com
Supporting users of Firebird and InterBase