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
