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
