The testing was done with Firebird 2.5.x x86 on Windows 7 x64.
Where other relational database platforms have plenty of opportunities to generate sequences (see for instance the below links on Oracle and SQL Server), with Firebird you can use a WITH RECURSIVE query construct that normally is being used to manage tree structures ([WayBack] PkLab – Firebird: Tree data mangement with recursive CTE).
However, that uses query stack which has a depth limit of 1024 levels. When you reach the limit, Firebird gives you an error like this:
with
recursive
sequence(n) as (
-- When you select more than 1024 values, this error occurs:
-- Error while fetching data: Too many concurrent executions of the same request
select 0 -- start
from rdb$database
union all
select sequence.n + 1
from sequence
where sequence.n < 1023 -- finish
)
select sequence.n
from sequence
--where sequence.n in (24, 38)
order by sequence.n
It however is a pretty quick and CU bound solution: on my system ([WayBack] AMD A8-7600 @ 3.1 Ghz), it runs 1000 records within ~0.1 seconds.
In such a short time, it’s hard to see how the speed is bound, so I wanted to go for some orders of magnitude more. In ~0.1 seconds, the processor executes about 0.3 * 10^9 cycles generating 1000 numbers which is ~ 300-thousand cycles per number. That sounds like a lot of cycles for so few numbers. Would this become a better ratio for more numbers?
Read the rest of this entry »