Hello,

i often need ranges in my program. Searching for bottlenecks i see that BETWEEN is sometimes
very slow.

Example table with some 100.000 records, fields (ID,ADATE,NUMBER,....)

secondary index on ID,ADATE,NUMBER

First example (slow)

SELECT * FROM table WHERE (ID BETWEEN 1000 AND 1000) AND (ADATE BETWEEN '01.01.2013' AND '01.01.2013') ORDER BY ID,ADATE,NUMBER
Elapsed time = 9 seconds resultset 8 records.
Plan shows firebird use correct index INDEX ID,ADATE,NUMBER. Many hardisk activity. Flamerobin reports 70.000 fetches.


Second example (rather quick)

SELECT * FROM table WHERE (ID = 1000) AND (ADATE = '01.01.2013') ORDER BY ID,ADATE,NUMBER
Elapsed time = 0.09 seconds

System cache is cleared before every test.

I can´t imagine what´s the reason for that. Same Test against MSSQL Server is allways quick.

Any help is welcome
Martin