On 12/2/08, Da Martian <[EMAIL PROTECTED]> wrote: > 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! > >
I have experienced the same, and my solution is exactly as noted above... programmatically create temp tables with appropriate indexes, and then query with those temp tables. No need to even drop the temp tables as they go away when the connection is dropped. Works like a charm, so there has been really no need to want to have core SQLite do the same for me, but I guess it might be nice. -- Puneet Kishor _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
