Michael -- I just wanted to follow up that we used your instructions to patch sqlite and ran our tests again with success. Thanks so much for your help!
Peter On 8/11/10 8:17 AM, Black, Michael (IS) wrote: > My patches are awating moderator approval so I figured I'd just send out the > info in case they get killed. > See below to fix your problem with a minimal patch. > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > ________________________________ > > From: Black, Michael (IS) > Sent: Tue 8/10/2010 4:41 PM > To: General Discussion of SQLite Database > Subject: RE:[sqlite] severe slowdown for sub-queries, solved in 3.7? > > > The patch that fixed your problem was on 2010-04-07 > > I've attached a patch from 3.6.23 to that version. > > It's 1849 lines of so that's not too bad for QA. > > I've also attached a context diff too. A bit easier to read if you need QA > to look at it. > > The sqlite3 source is too big for an attachment. > > If you want to reproduce you just need to clone the fossil repository and > then update like this: > fossil clone http://www.sqlite.org/src private-project.fossil > fossil open private-project.fossil > fossil update 2010-04-07 > mkdir bld > cd bld > ../configure > make > > If you update to any prior dates your slowdown will appear again. You need > to "make clean" in between fossil updates. > > > Michael D. Black > Senior Scientist > Advanced Analytics Directorate > Northrop Grumman Information Systems > > > ________________________________ > > From: sqlite-users-boun...@sqlite.org on behalf of Peter Pawlowski > Sent: Tue 8/10/2010 2:31 PM > To: General Discussion of SQLite Database > Subject: EXTERNAL:[sqlite] severe slowdown for sub-queries, solved in 3.7? > > > > Hello -- we recently noticed that some SQL statements containing > subqueries (see below) are very slow in 3.6.23 as compared to the > version we were using before, 3.3.17. I tested the newest release of > 3.7.0.1 and the queries are fast again. > > The problem is that this is a showstopper for us, but we cannot > immediately upgrade to 3.7, it is just too new and it takes a lot of QA > resources to do so. We'd prefer a more stable solution in the short term. > > So I'm trying to figure out if this is a known issue that was fixed for > 3.7 in a way that we might be able to patch 3.6. I've been looking for > clues in changelogs and tickets but haven't found anything promising > yet. Can anyone help? > > I'm including an example of our query and database... > > This is slow: > > select count (*) from reporting > left outer join (select * from type) as A0 > on a0.id = reporting.id; > > This is fast: > > select count (*) from reporting > left outer join type as A0 > on a0.id = reporting.id; > > Download the database (~100kb) from here: > > http://peterpawlowski.com/db.gz > > > Thanks, > Peter Pawlowski > > -- > Peter Pawlowski | Senior Software Engineer > Office: +1.412.422.2499 x116 > pawlow...@vivisimo.com | Connect: www.vivisimo.com > Vivisimo - Information Optimized > > _______________________________________________ > 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 -- Peter Pawlowski | Senior Software Engineer Office: +1.412.422.2499 x116 pawlow...@vivisimo.com | Connect: www.vivisimo.com Vivisimo - Information Optimized _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
