hi again,
I found my error so timings are :
timing with medium sudoku example :
'1....7.9..3..2...8..96..5....53..9...1..8...26....4...3......1..4......7..7...3..'
2 nested with = 3.32 sec
1 nested with = 1.7 sec
3 nested with = 2.65 sec
(1 nested with which could be 3 nested with) = 1.09 sec
** 3 nested with version **
with digits(z, lp) AS (
select '1' as z, 1 as lp
UNION ALL SELECT
CAST(lp+1 AS TEXT), lp+1 FROM digits WHERE lp<9
),
y(r) as (WITH RECURSIVE col(c) AS (
select 81 as c
union all
select c-1 from col where c>1
) select * from col)
, neighbors(r,n) as (select r, ((r-1)/9)*9 + lp from y, digits
union all select r, ((r-1)%9) + (lp-1)*9 + 1 from y, digits
union all select r, (((r-1)/3) % 3) * 3
+ ((r-1)/27) * 27 + lp
+ ((lp-1) / 3) * 6 from y, digits)
, goods (c,n) as (select * from neighbors where r <>n)
,input(sud) AS (
VALUES(
'1....7.9..3..2...8..96..5....53..9...1..8...26....4...3......1..4......7..7...3..'
)
),
/* The tricky bit. */
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 goods AS lp
WHERE ind=lp.c and z.z = substr(s, n, 1)
)
)
SELECT * FROM x WHERE ind=0;
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
