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