I still consider it a work around for adhoc queries. Programatically I can of course use it easily, but when analysing data one runs many adhoc queires which you change minute on minute. Having to create temp tables for each change and give it a new name for each change is a real pain.
Further given platforms like Oracle dont appear to suffer from this problem, I assume (as dangerous as that is) that they actually do create implicit indices. It is a nice to have I agree, but its big win nice to have! The ability to build indices on temp tables already exists in Sqlite, surely it cant be too hard to apply this when building temp tables from subqueries as you must have parsed the join criteria to be able join the tables. Thanks for the reply, S On Tue, Dec 2, 2008 at 3:57 PM, P Kishor <[EMAIL PROTECTED]> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
