--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:

>
> In your case, this of course only matters if there are lots of records
> meeting the criteria before considering FIRST. There are two ways to

try

> to make this quicker, one of them to tighten your requirements:
>
> select first 1000 * from A
> where update_time between '13-JUN-2006 00:00:00'
> and '13-JUN-2007 00:00:00'
> and id between 2950000 and 3000000
> order by id+0 desc
>
> The other is to try without any index at all (since a very large

portion

> of the data satisfy your where clause):
>
> select first 1000 * from A
> where update_time between '13-JUN-2006 00:00:00'
> and '13-JUN-2007 00:00:00'
> and id+0 between 2000 and 3000000
> order by id+0 desc
>
> If this doesn't help, I would consider writing a stored procedure.

Yes, this is the way I believe I shall take. Because I can't decide
the range of ID to retrieve first 1000 records satisfying the
criteria, I try using stored procedure to query the table block by
block. This is much faster :-)

>
> Still, I thought 16 minutes simply seemed too long, so I set up a small
> test environment with 3.2 million records - admittedly only three rows
> that were duplicated (the ID field set through a trigger so that wasn't
> duplicated) and using a DATE field rather than a DATETIME field.
>
> Running
>
> select first 1000 *
> from TESTA
> where ID between 2000 and 3000000
> and UPDATEDATE between '1.5.2006' and '30.4.2007'
> order by ID desc
>
> I do get the same plan as you report, but it executes in less than half
> a second! Changing to 'order by ID+0' increases the time to 12 seconds,
> which is also far less than 16 minutes. So there is something very
> different between our two Firebird 1.5.4 databases. Is 'A' really a
> table in your case or a view/stored procedure? What does the exact
> statement look like?
>
> Set
>

I did some more tests and seems the poor performance was related to
the machine where I run the client as well. I have a machine_A with
scalability test going on to insert data into another fb database (so
it's kind of loaded). Then I tested the query on a different database
on this machine by IBExpert, and it takes about 50 seconds to finish
the query. But if I connect to the database on machine_A from another
machine_B, the query comes out in a couple of seconds. I can't see the
reason though because client shouldn't play any role on query speed...

BTW, the stored procedures completes in less a second even when I run
it on machine_A.

Thanks everyone!!