Martin wrote:
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.
Ann W. Harrison answers:
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.
Svein Erling Tysvær adds:
One thing you cannot see by using a composite index, is which part of the index is in use. In your case, it means that you cannot see why example A is slow. Ann has shown you how Firebird actually would use the index and why example A is slow. Using separate indexes for each field has the benefit of making it easier to see why things are slow. In your particular case, it probably wouldn't have seen the difference, since I'd expect both examples to be quick with separate indexes (unlike most other databases, Firebird can use several indexes for each tuple in a query), maybe not 0.09 seconds, but I'd be greatly surprised if it took more than 0.2 seconds.
Another thing that can hide things from you, is your use of ORDER BY. Generally, the way you use ORDER BY may be good for production, but when trying to see if the index is used for the WHERE or JOIN clause, it might be better either not to include the ORDER BY or adding +0 or something to avoid seeing indexes used for ordering the result set (if you delete your WHERE clause in your examples, the index might still to be used due to the ORDER BY).
Thomas Steinmaurer adds:
SQL Server allows bi-directional traversal at leaf pages due to double-linked list and therefore doesn't need to go through the index tree from top to bottom periodically. Perhaps the result set is also entirely covered by the (clustered) index, which means no additional lookup from the index key to the real row.
But, SQL Server has other gotchas, which I don't miss in Firebird.