Hi
I have continious issues with subquery performance when subqueries are used
for joins. It crops up all the time my daily work.
If you create a derived table using a subquery and use it in a join SQLite
performance is abysmal. However if you make a temp table from said subquery
and index this temp table on the join keys, it goes at incredible speed.
Examples include a query which takes over 2 hours and doesnt complete as I
killed it, to running in under 10 seconds if use the temp table pattern.
This pattern of the temp table has to be repeated for almost any data
analysis I do as SQLite subquery performance with joins is so bad.
To recreate the problem simple create two subqueries which produce say 100
000 records each with composite integer keys and join them.
e.g
Table1 (Key1, Key2, Key3, Value)
Table2 (Key1, Key2, Key3, Value)
select *
from
(select Key1, Key2, sum(Value) as Value) from Table1 group by Key1,
Key2) t1 join
(select Key1, Key2, sum(Value) as Value) from Table2 group by Key1,
Key2) t2 on
(t1.Key1 = t2.Key1 and
t2.Key2 = t2.Key2)
Make sure T1 and Most esp T2 have large volumes of records to highlight the
problem, eg. 100 000 each does the job. >2 hours versus 10 seconds on my
hardware.
Can SQLite be altered to automatically create an index on subqueries used as
joins or lookups for the key fields used in the join or lookup. This would,
in my experience and opinion make SQLite so much more effective. The cost in
time of creating said indices is usually less 1 second on my hardware and
examples and saves hours!
Thanks,
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
