>>- my badly written sql takes about 3'40", (on my windows pc) >>- your neater version takes about 7'20",(on my windows pc) >>==> Do you see the same ratio ? I don't catch why yours is slower. > >One uses tables for the join constants and the other (slower one) >dynamically regenerates the constant table each time it is needed by re- >running the recursive CTE which dynamically generates the constants. > >Or at least that is what it looks like when you request SQLite to EXPLAIN >itself ... > >Though I could be mistaken ...
If you remove 'digits' from being a CTE to being a table, both execute "almost" the same speed. The remaining differential is that in the first case, the digits table only contains text, and the text is repetitively converted to an integer for use in the correlated subquery in the recursive CTE. This adds about 15-20% overhead compared to carrying both the text and integer version of the digit in the digits table and using the field with the appropriate field type in the appropriate place. Several million conversions of text to integer add up to a significant number of cycles. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
