--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:

>
> At 04:32 AM 15/03/2007, you wrote:
> >I´m looking for a faster way to read backwards inside a table.
> >
> >The Table has more than 400.000 entries.
> >
> >
> >unique index key bsysid,bclid,order_no Ascending
> >
> >1. Way
> >
> >SELECT first 1 order_no FROM ORDER_HEADER WHERE bsysid = 3 AND bclid =
> >1 AND order_no < 300361 order by order_no desc
> >
> >Plan
> >PLAN SORT ((ORDER_HEADER INDEX (ORDER_HEADER_KEY)))
> >
> >Adapted Plan
> >PLAN SORT ((ORDER_HEADER INDEX (ORDER_HEADER_KEY)))
> >
> >------ Performance info ------
> >Prepare time = 0ms
> >Execute time = 579ms
> >Avg fetch time = 579,00 ms
> >Current memory = 2.402.128
> >Max memory = 2.484.652
> >Memory buffers = 8.192
> >Reads from disk to cache = 8.348
> >Writes from cache to disk = 0
> >Fetches from cache = 226.038
> >
> >2. Way better bad not good.....
> >
> >SELECT max(order_no) FROM ORDER_HEADER WHERE bsysid = 3 AND bclid = 1
> >AND order_no < 300361
> >
> >Plan
> >PLAN (ORDER_HEADER INDEX (ORDER_HEADER_KEY))
> >
> >Adapted Plan
> >PLAN (ORDER_HEADER INDEX (ORDER_HEADER_KEY))
> >
> >------ Performance info ------
> >Prepare time = 0ms
> >Execute time = 500ms
> >Avg fetch time = 500,00 ms
> >Current memory = 2.401.612
> >Max memory = 2.484.652
> >Memory buffers = 8.192
> >Reads from disk to cache = 8.348
> >Writes from cache to disk = 0
> >Fetches from cache = 226.038
> >
> >
> >I´ve created an other INDEX like
> >
> >unique index key bsysid,bclid,order_no Descending
> >
> >and same combinations of this indexes inside the SQL select
> >statment, but the result is the same...
> >
> >
> >My question now has someone a better idea to save this problem
> >with a better response time ?
>
> Yes, don't make composite indexes in the belief
> that they are needed for multiple-key
> searches. Remove the compound indexes. Instesad,
> index the search columns individually and include
> a descending index for any column that needs it - order_no in this case.
>
> ./heLen
>

Re Helen,

I´ve changed the key to none compound index key.

is now ID......

bat the result is diminish a with compound indexes.......

here the info for this test....


SELECT FIRST 1 max(id)
FROM ORDER_HEADER WHERE id < 844429225399265

Plan
PLAN (ORDER_HEADER INDEX (ORDER_HEADER_ID_KEY))

Adapted Plan
PLAN (ORDER_HEADER INDEX (ORDER_HEADER_ID_KEY))

------ Performance info ------
Prepare time = 0ms
Execute time = 1s 828ms
Avg fetch time = 1.828,00 ms
Current memory = 2.927.148
Max memory = 2.931.632
Memory buffers = 8.192
Reads from disk to cache = 17.406
Writes from cache to disk = 0
Fetches from cache = 1.576.549


a better result is if I define a range ....

SELECT FIRST 1 max(id)
FROM ORDER_HEADER WHERE id > (844429225399265-300001)
and id < 844429225399265

Plan
PLAN (ORDER_HEADER INDEX (ORDER_HEADER_ID_KEY))

Adapted Plan
PLAN (ORDER_HEADER INDEX (ORDER_HEADER_ID_KEY))

------ Performance info ------
Prepare time = 0ms
Execute time = 641ms
Avg fetch time = 641,00 ms
Current memory = 2.842.572
Max memory = 2.952.044
Memory buffers = 8.192
Reads from disk to cache = 9.640
Writes from cache to disk = 0
Fetches from cache = 455.323

I think no good idea to search backwards inside the firebird
database.

WoWi