> i run the same test with MSSQL and look to the queryplan.
> MSSQL does it like i expect, it use the index range of the combined index and skips all that isn´t needed. It is allways rather quick.
> Even a real range where ID BETWEEN 1 AND 3 is allways quick (few msec).
> My App works many years with very good performance on MS SQL. Only on firebird i see this problem.
> Many people say firebird is rather quick. In this case it is not.
> I have switched a customers database from MSSQL to Firebird and at bigger reports the application seems to hang. (After minutes it comes back with the correct results). It was not possible to work with it.
> Switched back the same code and the same database to MS-SQL Server and the same calculation needs only a few seconds.
> With my improvement with = instead of BETWEEN or adding seperate indices it works even on firebird. Not so smooth but ok.
> Maybe Firebird 3.0 does better optimizations.

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. ;-)


--
With regards,
Thomas Steinmaurer
http://www.upscene.com/