Re: [sqlite] Threading makes SQLite 3x slower??

What is the output of "EXPLAIN QUERY PLAN " ? -- View this message in context: http://sqlite.1065341.n5.nabble.com/Threading-makes-SQLite-3x-slower-tp62243p70582.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing

Re: [sqlite] Threading makes SQLite 3x slower??

On 4 Aug 2011, at 20:40, Seth Price wrote: > so THREADSAFE=2 should work fine (as I understand it). What makes you think it isn't? > [...] it destroys performance. My guess is that you've moved the bottle-neck to your disk, and are suffering from seek latency.

Re: [sqlite] Threading makes SQLite 3x slower??

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

Re: [sqlite] Threading makes SQLite 3x slower??

The table with the first five columns is the R*tree table, and thus already has an index. I just tried adding an index to col5 also, but no change in performance. ~Seth On Aug 4, 2011, at 3:27 PM, Simon Slavin wrote: > > On 4 Aug 2011, at 9:59pm, Seth Price wrote: > >> Those fields span

Re: [sqlite] Threading makes SQLite 3x slower??

On 4 Aug 2011, at 9:59pm, Seth Price wrote: > Those fields span two tables. Then create one index for each table. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

Those fields span two tables. ~Seth via iPhone On Aug 4, 2011, at 2:45 PM, Simon Slavin wrote: > > On 4 Aug 2011, at 8:40pm, Seth Price wrote: > >> SELECT class FROM data_r JOIN data USING (rowNum) WHERE ?1 < col0min AND >> col0max < ?2 AND ?3 < col1min AND col1max <

Re: [sqlite] Threading makes SQLite 3x slower??

On 4 Aug 2011, at 8:40pm, Seth Price wrote: > 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

Re: [sqlite] Threading makes SQLite 3x slower??

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

Re: [sqlite] Threading makes SQLite 3x slower??

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

Re: [sqlite] Threading makes SQLite 3x slower??

I tried one index on all columns originally, but it didn't help nearly as much as the R*tree. ~Seth On Aug 4, 2011, at 10:26 AM, Simon Slavin wrote: > > On 4 Aug 2011, at 5:13pm, Seth Price wrote: > >> They were all simple indices on one or more columns, so if you have an idea >> on a more

Re: [sqlite] Threading makes SQLite 3x slower??

On 4 Aug 2011, at 5:13pm, Seth Price wrote: > They were all simple indices on one or more columns, so if you have an idea > on a more complex index, I'd apply it and test it out. That's not going to be much use, is it ? A single SELECT which tests all those different columns can only use one

Re: [sqlite] Threading makes SQLite 3x slower??

> Any threads or processes would still be in contention for the same resource: > access to that file on disk. Midway through the run, `iostat` is showing me exactly 0 disk accesses, so I'm assuming it's a locking problem and not a physical I/O bottleneck. I'm hoping that you folks could give

Re: [sqlite] Threading makes SQLite 3x slower??

ng. > > > > > > > > > > Michael D. Black > > Senior Scientist > > NG Information Systems > > Advanced Analytics Directorate > > > > ____ > From: sqlite-users-boun...@sqlite.org [sqlite-users

Re: [sqlite] Threading makes SQLite 3x slower??

On 4 Aug 2011, at 4:44pm, Seth Price wrote: > I was hoping that SQLite was doing that with the JOIN statement. It's > narrowing down the results with the query from the R*table (col*min & > col*max). A large query will have ~14k rows at this point. Then JOINs with > the original table (data)

Re: [sqlite] Threading makes SQLite 3x slower??

On Aug 4, 2011, at 1:54 AM, Eduardo Morras wrote: > > They block each other not trying to get a lock, but trying to get > access to disk and cache. Disk access time and cache is shared > between all threads and if all threads needs access to different > parts of the db they will figth like

Re: [sqlite] Threading makes SQLite 3x slower??

t; NG Information Systems > Advanced Analytics Directorate > > > > > From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on > behalf of Seth Price [s...@pricepages.org] > Sent: Wednesday, August 03, 2011 9:07 PM > To: sqlite-users@sqlite.org >

Re: [sqlite] Threading makes SQLite 3x slower??

Directorate From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Seth Price [s...@pricepages.org] Sent: Wednesday, August 03, 2011 9:07 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Threading makes SQLite 3x slower?? Hey all, I have

Re: [sqlite] Threading makes SQLite 3x slower??

users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Seth Price [s...@pricepages.org] Sent: Wednesday, August 03, 2011 9:07 PM To: sqlite-users@sqlite.org Subject: EXT :[sqlite] Threading makes SQLite 3x slower?? Hey all, I have a small DB bound application that I'm working on

Re: [sqlite] Threading makes SQLite 3x slower??

At 06:53 04/08/2011, you wrote: >I was hoping they wouldn't block each other because it's a read >lock. I tried making an index on all the columns, but R*tree table + >JOIN that I'm using runs about 10x faster. I might have done >something wrong, so I'm open to suggestions on a better index,

Re: [sqlite] Threading makes SQLite 3x slower??

I was hoping they wouldn't block each other because it's a read lock. I tried making an index on all the columns, but R*tree table + JOIN that I'm using runs about 10x faster. I might have done something wrong, so I'm open to suggestions on a better index, though. I don't think that UPDATEing

Re: [sqlite] Threading makes SQLite 3x slower??

On 4 Aug 2011, at 3:07am, Seth Price wrote: > 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

[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