So far I've sped it up by 2x-3x by increasing the cache size by 10x, removing some extraneous SQL, and moving a bit of processing in-app. But using the full dataset with two threads instead of one still results in a 3x-5x time increase with either THREADSAFE=1 or 2. The fastest is single-threaded with THREADSAFE=0. My prepared queries now look like this:
SELECT class FROM data_r JOIN data USING (rowNum) WHERE ?1 < col0min AND col0max < ?2 AND ?3 < col1min AND col1max < ?4 AND ?5 < col2min AND col2max < ?6 AND ?7 < col3min AND col3max < ?8 AND ?9 < col4min AND col4max < ?10 AND ?11 < col5 AND col5 < ?12; Am I configuring locking right? I have 17 million queries like the above. Since they are all read-only, I shouldn't be taking such a hit. Every thread has it's own DB connection and prepared statements, so THREADSAFE=2 should work fine (as I understand it). Instead it destroys performance. Thanks, Seth On Aug 4, 2011, at 12:36 PM, Seth Price wrote: > Removing "COUNT(*) AS count" and "GROUP BY class" and doing it in-program > shaved ~10% off of the time. I'll keep it. :) > ~Seth > > On Aug 4, 2011, at 11:30 AM, Eduardo Morras wrote: > >> >> Oks, another let's try another thing/think. >> >> Try the select without the COUNT(*): >> >> SELECT class FROM data_r JOIN data USING (rowNum) WHERE 57 < col0min >> AND col0max < 61 AND 52 < col1min AND col1max < 56 AND 66 < col2min >> AND col2max < 70 AND 88 < col3min AND col3max < 92 AND 133 < col4min >> AND col4max < 137 AND 57 < col0 AND col0 < 61 AND 52 < col1 AND col1 >> < 56 AND 66 < col2 AND col2 < 70 AND 88 < col3 AND col3 < 92 AND 133 >> < col4 AND col4 < 137 AND 81 < col5 AND col5 < 85 GROUP BY class; >> >> In some rdbms (don' know in sqlite), count, avg, sum, etc... implies >> a table scan, making the select very slow. >> >> HTH >> >> >> _______________________________________________ >> 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
