On Sat, Feb 16, 2013 at 5:37 PM, ml600f <ml600f@...> wrote:

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

Use a separate index for each column that you use in a range query.
Firebird can combine ranges from separate indexes but cannot use more
than one range in a particular index. Index retrievals must be contiguous.
For example if you have an index with pairs
(A,1) (A, 2), (A,3), (B,1) (B, 2), (B,3), (C,1) (C, 2), (C,3),

and you asked for field1 between A and B and field2 between 1 and 2, you'd
want the first 2 pairs, then skip on, then the next two pairs. Because
Firebird index retrievals can't do the "skip", it ignores the second term
and just returns everything with field1 between A and B and lets a higher
level sieve throw out records that don't match the second condition.

secondary index on ID,ADATE,NUMBER

SELECT * FROM table
WHERE (ID BETWEEN 1000 AND 1000) AND (ADATE BETWEEN '01.01.2013' AND
'01.01.2013')
ORDER BY ID,ADATE,NUMBER

Firebird doesn't do a lot of optimization tricks like noticing that the end
points of a between are the same. That's probably how MS SQL optimizes
that query.

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

As you would expect.

Good luck,

Ann
IBPhoenix, commercial support for Firebird


[Non-text portions of this message have been removed]