fsqlite、tursodb 和 sqlite3 递归SQL性能比较

fsqlite、tursodb都是用rust重写的兼容sqlite3数据库。
对它们分别用简单和较难的数独数据执行同样的递归求解SQL(来自sqlite官方文档)

WITH RECURSIVE
  input(sud) AS (
    VALUES('....754..........8.8.19....3....1.6........34....6817.2.4...6.39......2.53.2.....'), 
    ('3.........5.7.3..8....28.7.7......43...........39.41.54..3..8..1...4....968...2..')
  ),
  digits(z, lp) AS (
    VALUES('1', 1)
    UNION ALL SELECT
    CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
  ),
  x(s, ind) AS (
    SELECT sud, instr(sud, '.') FROM input
    UNION ALL
    SELECT
      substr(s, 1, ind-1) || z || substr(s, ind+1),
      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
     FROM x, digits AS z
    WHERE ind>0
      AND NOT EXISTS (
            SELECT 1
              FROM digits AS lp
             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
                OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
                OR z.z = substr(s, (((ind-1)/3) % 3) * 3
                        + ((ind-1)/27) * 27 + lp
                        + ((lp-1) / 3) * 6, 1)
         )
  )
SELECT s FROM x WHERE ind=0;

WITH RECURSIVE
  input(sud) AS (
    VALUES('53..7....6..195....98....6.8...6...34..8.3..17...2...6.6....28....419..5....8..79')
  ),
  digits(z, lp) AS (
    VALUES('1', 1)
    UNION ALL SELECT
    CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
  ),
  x(s, ind) AS (
    SELECT sud, instr(sud, '.') FROM input
    UNION ALL
    SELECT
      substr(s, 1, ind-1) || z || substr(s, ind+1),
      instr( substr(s, 1, ind-1) || z || substr(s, ind+1), '.' )
     FROM x, digits AS z
    WHERE ind>0
      AND NOT EXISTS (
            SELECT 1
              FROM digits AS lp
             WHERE z.z = substr(s, ((ind-1)/9)*9 + lp, 1)
                OR z.z = substr(s, ((ind-1)%9) + (lp-1)*9 + 1, 1)
                OR z.z = substr(s, (((ind-1)/3) % 3) * 3
                        + ((ind-1)/27) * 27 + lp
                        + ((lp-1) / 3) * 6, 1)
         )
  )
SELECT s FROM x WHERE ind=0;

C:\d>echo .exit | timer64 sqlite351 -batch -init sudomid3.sql
693875412145632798782194356357421869816957234429368175274519683968743521531286947
387419526259763418641528379716285943594631782823974165472396851135842697968157234


Kernel  Time =     0.015 =    1%
User    Time =     1.140 =   97%
Process Time =     1.156 =   98%    Virtual  Memory =      4 MB
Global  Time =     1.168 =  100%    Physical Memory =      8 MB

C:\d>echo .exit | timer64 fsqlite --batch --init sudomid3.sql
693875412145632798782194356357421869816957234429368175274519683968743521531286947
387419526259763418641528379716285943594631782823974165472396851135842697968157234


Kernel  Time =     0.250 =    0%
User    Time =   126.187 =   99%
Process Time =   126.437 =   99%    Virtual  Memory =    144 MB
Global  Time =   126.916 =  100%    Physical Memory =    137 MB

C:\d>type sudomid3.sql | timer64 tursodb080
┌───────────────────────────────────────────────────────────────────────────────────┐
│ s                                                                                 │
├───────────────────────────────────────────────────────────────────────────────────┤
│ 693875412145632798782194356357421869816957234429368175274519683968743521531286947 │
├───────────────────────────────────────────────────────────────────────────────────┤
│ 387419526259763418641528379716285943594631782823974165472396851135842697968157234 │
└───────────────────────────────────────────────────────────────────────────────────┘


Kernel  Time =     0.015 =    0%
User    Time =     3.187 =   99%
Process Time =     3.203 =   99%    Virtual  Memory =     35 MB
Global  Time =     3.203 =  100%    Physical Memory =     31 MB

C:\d>echo .exit | timer64 sqlite351 -batch -init sudo.sql
534678912672195348198342567859761423426853791713924856961537284287419635345286179


Kernel  Time =     0.015 =   31%
User    Time =     0.031 =   63%
Process Time =     0.046 =   95%    Virtual  Memory =      1 MB
Global  Time =     0.049 =  100%    Physical Memory =      6 MB

C:\d>echo .exit | timer64 fsqlite --batch --init sudo.sql
534678912672195348198342567859761423426853791713924856961537284287419635345286179


Kernel  Time =     0.078 =    1%
User    Time =     4.203 =   97%
Process Time =     4.281 =   99%    Virtual  Memory =      8 MB
Global  Time =     4.298 =  100%    Physical Memory =     14 MB

C:\d>
C:\d>type sudo.sql | timer64 tursodb080
┌───────────────────────────────────────────────────────────────────────────────────┐
│ s                                                                                 │
├───────────────────────────────────────────────────────────────────────────────────┤
│ 534678912672195348198342567859761423426853791713924856961537284287419635345286179 │
└───────────────────────────────────────────────────────────────────────────────────┘


Kernel  Time =     0.015 =   12%
User    Time =     0.109 =   84%
Process Time =     0.125 =   97%    Virtual  Memory =     35 MB
Global  Time =     0.128 =  100%    Physical Memory =     24 MB

如果是linux，用time代替timer64, 用cat代替type命令

转载自 CSDN-专业IT技术社区

原文链接：https://blog.csdn.net/l1t/article/details/165242532