> You didn't show your timing results or say what kind of machine you're > running on.
I'm running on a 2.26 GHz Mac Pro with 8 physical cores and 16 GB of RAM. > SQL error (635): near "ORDER": syntax error You're probably seeing that error because you need to recompile with "-DSQLITE_ENABLE_UPDATE_DELETE_LIMIT". You would need to rebuild the parser, but I don't think it affects the results that we're looking at. So ignore the error. :) > What I would do is one query and split the results of that query between > threads. As is, the result from each query is ~1-3 rows that don't require much processing. A profiler says I spend about 99% of my time in SQLite. ~Seth On Aug 4, 2011, at 6:33 AM, Black, Michael (IS) wrote: > You didn't show your timing results or say what kind of machine you're > running on. > I'm also seeing > Calculating Subset Sample... > SQL error (635): near "ORDER": syntax error > > I'm running Linux, sqlite 3.7.5, E5520 2.27Ghz Intel with 16 threads possible. > > Here's my timings just on the first few rows of your timing output shows 2 > threads is a touch better. > 1 thread > ( 0) Rows Proc: 5000 Avg: 0.00144 Elapsed: 7.20 > ( 0) Rows Proc: 10000 Avg: 0.00104 Elapsed: 10.37 > ( 0) Rows Proc: 15000 Avg: 0.00098 Elapsed: 14.77 > ( 0) Rows Proc: 20000 Avg: 0.00092 Elapsed: 18.36 > ( 0) Rows Proc: 25000 Avg: 0.00084 Elapsed: 21.08 > > 2 threads > ( 1) Rows Proc: 5000 Avg: 0.00103 Elapsed: 5.20 > ( 1) Rows Proc: 10000 Avg: 0.00104 Elapsed: 10.45 > ( 1) Rows Proc: 15000 Avg: 0.00103 Elapsed: 15.53 > ( 0) Rows Proc: 5000 Avg: 0.00419 Elapsed: 20.96 > ( 1) Rows Proc: 20000 Avg: 0.00106 Elapsed: 21.23 > > 3 threads > ( 1) Rows Proc: 5000 Avg: 0.00536 Elapsed: 26.91 > ( 2) Rows Proc: 5000 Avg: 0.00598 Elapsed: 30.05 > ( 1) Rows Proc: 10000 Avg: 0.00535 Elapsed: 53.67 > ( 2) Rows Proc: 10000 Avg: 0.00612 Elapsed: 61.36 > ( 1) Rows Proc: 15000 Avg: 0.00537 Elapsed: 80.75 > > What I would do is one query and split the results of that query between > threads. > > You can use OpenMP to do that. > > > Michael D. Black > Senior Scientist > NG Information Systems > Advanced Analytics Directorate > > > > > From: [email protected] [[email protected]] on > behalf of Seth Price [[email protected]] > Sent: Wednesday, August 03, 2011 9:07 PM > To: [email protected] > Subject: EXT :[sqlite] Threading makes SQLite 3x slower?? > > > Hey all, I have a small DB bound application that I'm working on. I expect > that it will take 10-20 days to run when I'm done with it. So I'm trying to > make it multithreaded. But after spending all afternoon getting threading > going, it runs on the order of 3x slower per query when I go from one to two > threads. > > Is this expected? > > The full DB has around 17 million rows in it, and for each row I am trying to > count all rows with similar characteristics, divided into different > classifications. I was already able to improve speed 10x by using the R*tree > extension to narrow my search. My queries look like this: > > SELECT class, COUNT(*) AS count 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; > > They take around 0.04 seconds each (times 17 million). I thought that I > should be able to access the DB from each thread without penalty because they > are read-only queries. I also tried making copies of the DB file to access a > different file with each thread (also slower). Oddly, there are no frequent > disk access while I run the program, so there must be caching somewhere. > > How can I make threading work? Download the example set and code here: > http://seth.bluezone.usu.edu/sqlite/ > > There is info on how I compile and run the program in the header. Use > NUM_THREADS to change the number. The stats on which thread is taking how > much time is printed under "Pruning Conflicting Examples..." and the > threading code is under "pruneEx(ExPtr ex)". I'm on a Mac Pro running 10.6.8. > > Thoughts? Comments? Ideas? > > Thanks, > Seth > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
