| Subject | Re: [firebird-support] slow: "select first(1) * from table where partnumber >= :search order by partnumber" |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-06-19T02:14:28Z |
At 01:03 AM 19/06/2005 +0200, you wrote:
able to determine which is the "first". There are two ways around this:
a) Do what one used to do before the SELECT FIRST syntax existed: use a
stored procedure to loop through your records
or
b) Work out whether SELECT FIRST is really what you want. For example, you
will get almost the same result from this, provided you have a descending
index on partnumber:
select t1.* from tablet1
where t1.partnumber = (select max(t2.partnumber) from table t2
where t2.partnumber >= :search)
The difference here is that you will get multiple rows if more than one row
matches the found partnumber. From the performance aspect, provided you
have that descending index, the subquery will find the maximum partnumber
in a single shot and return the matching row(s) on a very short search.
engine decided to use your index for the ordering part of the operation, it
is probably not useful for getting a quick result. The Firebird engine
can't "flip" indexes. When you're looking for "higher" values, having a
descending index available for the engine to choose might be more helpful
than providing only an ascending one. However, the more you can do (with
limiting WHERE criteria) to reduce the number of rows in intermediate set,
the sooner your single row result will come.
./heLen
>environment: Firebird server 1.5.2 on Linux (Suse 9.2), WindowsWell, the query has to generate the entire set and then order it, to be
>clients with IBObject 4.5.
>
>I use the following selects to search for a single record equal or
>greater than the searchstring:
>"select first(1) * from table where partnumber >= :search order by
>partnumber"
>
>It's working well, but kind of slow on bigger tables (>20.000 records).
>It's faster near the the of the table and very slow at the beginning
>(ordered by partnumber).
>While digging through the newsgroups and mailings lists, I found
>somewhere that a "select first(1) ..." is internally generating the full
>sort and then returning the single record.
>My question:
>1.) Is there a way to optimize this query?
able to determine which is the "first". There are two ways around this:
a) Do what one used to do before the SELECT FIRST syntax existed: use a
stored procedure to loop through your records
or
b) Work out whether SELECT FIRST is really what you want. For example, you
will get almost the same result from this, provided you have a descending
index on partnumber:
select t1.* from tablet1
where t1.partnumber = (select max(t2.partnumber) from table t2
where t2.partnumber >= :search)
The difference here is that you will get multiple rows if more than one row
matches the found partnumber. From the performance aspect, provided you
have that descending index, the subquery will find the maximum partnumber
in a single shot and return the matching row(s) on a very short search.
>2.) Any improvements in FB2? As there is an index on theIn this case, it's not a question of "improvements in the engine". If the
>partnumber column, the plan should be able to return the data
>quickly using it.
engine decided to use your index for the ordering part of the operation, it
is probably not useful for getting a quick result. The Firebird engine
can't "flip" indexes. When you're looking for "higher" values, having a
descending index available for the engine to choose might be more helpful
than providing only an ascending one. However, the more you can do (with
limiting WHERE criteria) to reduce the number of rows in intermediate set,
the sooner your single row result will come.
./heLen