Many CTE queries are just some mini-algorithms with iteration and only
last row is required. I just wondered whether it's easy to do this
without "order by ... " of the outer query (also mentioned in my reply
about CTE sqrt). There's a solution, but the good news is that
probably one rarely needs such optimization because of effectiveness
of sqlite in-memory B-tree sort. Anyway, the results are below.
A synthetic test of a query interating until 1,000,000
with recursive
testval(depth, val) as
(
select 1, 0
UNION ALL
select depth + 1, val + 1 from testval where depth <= 1000000
)
select val from testval order by depth desc limit 1
Time:
4 sec. 20 ms
Explain query plan
"SCAN TABLE testval"
"COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)"
"SCAN SUBQUERY 1"
"USE TEMP B-TREE FOR ORDER BY"
We add an endmark and where condition moves to the select query so we
get rid of "order by" completely
with recursive
testval(endmark, depth, val) as
(
select 0, 1, 0
UNION ALL
select (case when depth < 1000000 then 0 else 1 end), depth + 1,
val + 1 from testval where endmark=0
)
select val from testval where endmark=1
Time:
2 sec 900 ms.
Explain query plan:
"SCAN TABLE testval"
"COMPOUND SUBQUERIES 0 AND 0 (UNION ALL)"
"SCAN SUBQUERY 1"
For the modified CTE sqrt from the other post the difference for
100,000 queries was 27 seconds vs 21 seconds. Not so much, but
sometimes it might make sense to do such optimization
Max
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
