Selecting a sequence of a million numbers in Firebird

selecting-a-million-sequential-numbers.A.sql

-- via :
-- - https://stackoverflow.com/questions/39751643/fastest-way-to-generate-range-of-numbers
-- - https://stackoverflow.com/questions/42750834/how-to-create-range-from-1-to-100-in-firebird-select-statement
-- - https://firebirdsql.org/refdocs/langrefupd21-select.html

with 
  digits(n) as ( -- for sextupledigits, this takes ~35-45 seconds
          select 0 from rdb$database             
    union select 1 from rdb$database
    union select 2 from rdb$database
    union select 3 from rdb$database
    union select 4 from rdb$database
    union select 5 from rdb$database
    union select 6 from rdb$database
    union select 7 from rdb$database
    union select 8 from rdb$database
    union select 9 from rdb$database
  ),
  tens(n) as (
    select 10*digits.n
    from digits
  ),
  hundreds(n) as (
    select 100*digits.n
    from digits
  ),
  tripledigits(n) as (
    select digits.n 
          + tens.n
          + hundreds.n
    from digits
    cross join tens
    cross join hundreds
    order by digits.n 
          + tens.n
          + hundreds.n
  ), 
  -- prepared in 0.065-0.0368 seconds, processed in 34.241-53.835 seconds - rows fetched: 250
  sextupledigits(n) as (
    select tripledigits.n 
          + tripledigits1000.n * 1000
    from tripledigits
    cross join tripledigits as tripledigits1000
    order by tripledigits.n 
          + tripledigits1000.n * 1000
  )
select sextupledigits.n 
from sextupledigits
--where sextupledigits.n in (24, 38) 
order by sextupledigits.n 

/*
PLAN SORT (SORT (JOIN (SORT (JOIN ((SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)(SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)(SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL), SORT (JOIN ((SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)(SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)(SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN (SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL))))))))
*/

selecting-a-million-sequential-numbers.B.sql

-- via :
-- - https://stackoverflow.com/questions/39751643/fastest-way-to-generate-range-of-numbers
-- - https://stackoverflow.com/questions/42750834/how-to-create-range-from-1-to-100-in-firebird-select-statement
-- - https://firebirdsql.org/refdocs/langrefupd21-select.html 

with
  recursive 
  digits(n) as ( -- for sextupledigits, this takes ~10-16 seconds
    select 0 -- start
    from rdb$database
    union all
    select digits.n + 1
    from digits
    where digits.n < 9 -- finish
  ),
  tens(n) as (
    select 10*digits.n
    from digits
  ),
  hundreds(n) as (
    select 100*digits.n
    from digits
  ),
  tripledigits(n) as (
    select digits.n 
          + tens.n
          + hundreds.n
    from digits
    cross join tens
    cross join hundreds
    order by digits.n 
          + tens.n
          + hundreds.n
  ), 
  -- prepared in 0.065-0.073 seconds, processed in 10.428-15.783 seconds - rows fetched: 250
  sextupledigits(n) as (
    select tripledigits.n 
          + tripledigits1000.n * 1000
    from tripledigits
    cross join tripledigits as tripledigits1000
    order by tripledigits.n 
          + tripledigits1000.n * 1000
  )
select sextupledigits.n 
from sextupledigits
--where sextupledigits.n in (24, 38) 
order by sextupledigits.n 

/*
PLAN SORT (SORT (JOIN (SORT (JOIN ((SEXTUPLEDIGITS TRIPLEDIGITS DIGITS RDB$DATABASE NATURAL)
PLAN JOIN ()(SEXTUPLEDIGITS TRIPLEDIGITS TENS DIGITS RDB$DATABASE NATURAL)
PLAN JOIN ()(SEXTUPLEDIGITS TRIPLEDIGITS HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN JOIN (), SORT (JOIN ((SEXTUPLEDIGITS TRIPLEDIGITS1000 DIGITS RDB$DATABASE NATURAL)
PLAN JOIN ()(SEXTUPLEDIGITS TRIPLEDIGITS1000 TENS DIGITS RDB$DATABASE NATURAL)
PLAN JOIN ()(SEXTUPLEDIGITS TRIPLEDIGITS1000 HUNDREDS DIGITS RDB$DATABASE NATURAL)
PLAN JOIN ())))))))
*/

selecting-a-million-sequential-numbers.C.sql

-- via :
-- - https://stackoverflow.com/questions/39751643/fastest-way-to-generate-range-of-numbers
-- - https://stackoverflow.com/questions/42750834/how-to-create-range-from-1-to-100-in-firebird-select-statement
-- - https://firebirdsql.org/refdocs/langrefupd21-select.html 

with 
  recursive
  doubledigits(n) as (
    select 0 -- start
    from rdb$database
    union all
    select doubledigits.n + 1
    from doubledigits
    where doubledigits.n < 99 -- finish
  ),
  doubledigits00(n) as (
    select doubledigits.n * 100
    from doubledigits
  ),
  quadrupledigits(n) as (
    select doubledigits.n 
          + doubledigits00.n
    from doubledigits
    cross join doubledigits00
    order by doubledigits.n 
          + doubledigits00.n
  ), 
  -- prepared in 0.061-0.078 seconds, processed in 6.493-8.381 seconds - rows fetched: 250
  sextupledigits(n) as (
    select doubledigits.n 
          + quadrupledigits.n * 100
    from doubledigits
    cross join quadrupledigits
    order by doubledigits.n 
          + quadrupledigits.n * 100
  )
select sextupledigits.n 
from sextupledigits
--where sextupledigits.n in (24, 38) 
order by sextupledigits.n 
/*
PLAN SORT (SORT (JOIN ((SEXTUPLEDIGITS DOUBLEDIGITS RDB$DATABASE NATURAL)
PLAN JOIN (), SORT (JOIN ((SEXTUPLEDIGITS QUADRUPLEDIGITS DOUBLEDIGITS RDB$DATABASE NATURAL)
PLAN JOIN ()(SEXTUPLEDIGITS QUADRUPLEDIGITS DOUBLEDIGITS00 DOUBLEDIGITS RDB$DATABASE NATURAL)
PLAN JOIN ())))))
*/

selecting-a-million-sequential-numbers.D.sql

-- via :
-- - https://stackoverflow.com/questions/39751643/fastest-way-to-generate-range-of-numbers
-- - https://stackoverflow.com/questions/42750834/how-to-create-range-from-1-to-100-in-firebird-select-statement
-- - https://firebirdsql.org/refdocs/langrefupd21-select.html 

with 
  recursive
  tripledigits(n) as (        
    -- When you select more than 1024, this error occurs:
    -- Error while fetching data:  Too many concurrent executions of the same request    
    select 0 -- start
    from rdb$database
    union all
    select tripledigits.n + 1
    from tripledigits
    where tripledigits.n < 999 -- finish
  ),
  tripledigits000(n) as (
    select tripledigits.n * 1000
    from tripledigits
  ),
  -- prepared in 0.0055-0.126 seconds, processed in 3.617-4.575 seconds - rows fetched: 250
  sextupledigits(n) as (
    select tripledigits.n 
          + tripledigits000.n
    from tripledigits
    cross join tripledigits000
    order by tripledigits.n 
          + tripledigits000.n
  )
select sextupledigits.n 
from sextupledigits
--where sextupledigits.n in (24, 38) 
order by sextupledigits.n 
/*
PLAN SORT (SORT (JOIN ((SEXTUPLEDIGITS TRIPLEDIGITS RDB$DATABASE NATURAL)
PLAN JOIN ()(SEXTUPLEDIGITS TRIPLEDIGITS000 TRIPLEDIGITS RDB$DATABASE NATURAL)
PLAN JOIN ())))
*/

selecting-a-million-sequential-numbers.E.sql

-- via :
-- - https://stackoverflow.com/questions/39751643/fastest-way-to-generate-range-of-numbers
-- - https://stackoverflow.com/questions/42750834/how-to-create-range-from-1-to-100-in-firebird-select-statement
-- - https://firebirdsql.org/refdocs/langrefupd21-select.html 

with 
  recursive
  tripledigits(n) as (        
    select 0 -- start
    from rdb$database
    union all
    select tripledigits.n + 1
    from tripledigits
    where tripledigits.n < 999 -- finish
  ),
  -- prepared in 0.043-0.078 seconds, processed in 3.705-5.023 seconds - rows fetched: 250
  sextupledigits(n) as (
    select tripledigitsBare.n 
          + tripledigits000.n * 1000
    from tripledigits tripledigitsBare
    cross join tripledigits tripledigits000
    order by tripledigitsBare.n 
          + tripledigits000.n * 1000
  )
select sextupledigits.n 
from sextupledigits
--where sextupledigits.n in (24, 38) 
order by sextupledigits.n
/*
PLAN SORT (SORT (JOIN ((SEXTUPLEDIGITS TRIPLEDIGITS RDB$DATABASE NATURAL)
PLAN JOIN ()(SEXTUPLEDIGITS TRIPLEDIGITS RDB$DATABASE NATURAL)
*/