Seems that using pragma temp_store=2 improves the speed. It now tooks 20 seconds. So now the penalty time is 9 seconds. It would possible to improve it?.
Regards. Iván 2013/6/17 Iván de Prado <i...@datasalt.com> > I have query where adding a simple "group by" by date is slowing the query > too much, from my point of view. > > The following query: > > SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where > ddate < '2013-08-01'; > > runs in 12 seconds. Approximately 423,327 rows scanned per second. 1 row > returned. The query plan is: > > SCAN TABLE a_idadvertiser_site_hostname_bench (~333333 rows) > > But adding the clause "group by ddate" slows too much the query: > > SELECT ddate, sum(hits) from a_idadvertiser_site_hostname_bench where > ddate < '2013-08-01' group by ddate; > > runs in 67 seconds. Approximately 75,819 rows scanned per second. 63 rows > returned. > > The query plan is now: > > SCAN TABLE a_idadvertiser_site_hostname_bench (~333333 rows) > USE TEMP B-TREE FOR GROUP BY > > That means that this query is running almost 6 times slower than the one > without the group by. How could that be? The number of group by buckets is > very small (just 63 different dates), so it would be enough to do a scan > and keep a hash table in memory with partial results. > > Any ideas about what's the problem and how could we solve it? > > The Sqlite version we are using is 3.7.14.1. > > Regards! > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
