On 4 Jan 2010, at 2:38pm, Jeremy Zeiber wrote: > That particular query runs in ~ 30 seconds with outerdetail.header or > header.headerid. I do have another query which gives the same result > that doesn't quite run as fast as the first, but it is certainly faster > than the second: > > SELECT headerid, > (SELECT (SELECT COUNT(DISTINCT data) FROM detail WHERE > headerid=header.headerid)-COUNT(DISTINCT one.data) FROM detail AS one > INNER JOIN detail AS two ON one.data=two.data WHERE > two.headerid<one.headerid AND one.headerid=header.headerid) AS newcount > FROM header GROUP BY headerid; > > That runs in about 3 seconds. Still, there are only a few thousand rows > in the test database, and the real data is going to have hundreds of > thousands of rows, and this is just a small portion of the query. Is > there any way to rewrite the query to a better performing one?
Do you have the appropriate indexes defined ? I see lots of matching and WHERE clauses and your query may not be finding an index that can do all that work for it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
