Re: [sqlite] Potential problem in SQLite 3.7.16.2
Thank you for providing this pre-release amalgamation ;-) I downloaded it immediately and compiled it into my software. The problem has been ++resolved++ and the performance is at least as good as with previous versions of SQLite. It even feels a bit faster, although I only could try it with a 20% sample of the typical data volume. Thank you for the excellent support. Much appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
Thank you for providing this pre-release amalgamation ;-) I downloaded it immediately and compiled it into my software. The problem has been ++resolved++ and the performance is at least as good as with previous versions of SQLite. It even feels a bit faster, although I only could try it with a 20% sample of the typical data volume. Thank you for the excellent support. Much appreciated. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
On Tue, Apr 30, 2013 at 3:34 AM, Mario M. Westphal wrote: > Hi, > > I just tried this (sorry dor the delay) but apparently I'm not having the > right toolset installed. > Please download the latest amalgamations from http://www.sqlite.org/draft/download.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
Hi, I just tried this (sorry dor the delay) but apparently I'm not having the right toolset installed. The make file requires gawk.exe (which I downloaded from sourceforge), but now it's complaing about a missing tclsh85... Since the previous SQLite version works fine I think I'll skip this and wait for the next official release. If the error still persists in that release, I'll post again. Thanks for your support. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
On Mon, Apr 22, 2013 at 8:20 AM, Mario M. Westphal wrote: > > So far I only used the Amalgamation. Looks like the files on your source > control server require me to build SQLite from source or at least run a > tool/script to build the amalgamation. > > I'm using Windows and Visual Studio so the standard Linux build tools and > scripting languages are not available. > Is there an easy way to create the amalgamation on Windows? Open an MSVC shell window and type: nmake /f makefile.msc sqlite3.c > Otherwise I will > have to change my project to try this change out. Which I can do, but it > will take some extra days before I'll find the time for that. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
So far I only used the Amalgamation. Looks like the files on your source control server require me to build SQLite from source or at least run a tool/script to build the amalgamation. I'm using Windows and Visual Studio so the standard Linux build tools and scripting languages are not available. Is there an easy way to create the amalgamation on Windows? Otherwise I will have to change my project to try this change out. Which I can do, but it will take some extra days before I'll find the time for that. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
On Thu, Apr 18, 2013 at 3:27 AM, Mario M. Westphal wrote: > > After downloading and compiling in the SQLite 3.7.16.2, we have a problem. > > The INSERT statement which takes only a few seconds with previous builds > now does not return, at least not within several minutes. > It's hard to debug the amalgation in Visual Studio but it looks like > SQLite would be caught in an internal loop inside the step() function call. > The problem, of course, has nothing to do with INSERT. The problem is that the 4-way join on the SELECT statement is choosing a different (and apparently much slower) query plan since the introduction of transitive constraint processing in 3.7.16. The latest check-in (http://www.sqlite.org/src/info/5f4907e1c6) contains yet another optimization which should make the query fast again. For the test case in question, the output is now nearly instantaneous. Please recompile using the latest SQLite source code (which you can download using the "ZIP archive" link on the check-in page mentioned in the previous paragraph) and let us know if this does not make the code run a lot faster. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
FWIW: The following script demonstrates the problem: CREATE TABLE t1(x INTEGER, y INTEGER); CREATE INDEX t1xy ON t1(x,y); CREATE INDEX t1y ON t1(y); CREATE TABLE t2(x INTEGER PRIMARY KEY, y INTEGER); CREATE TABLE t3(x INTEGER PRIMARY KEY, y INTEGER); CREATE INDEX t3y ON t3(y); .wheretrace off explain query plan SELECT * FROM t1, t2, t3 WHERE t1.x IN (1,2,3,4) AND t1.y IN (1,2,3,4) AND t1.y = t2.x AND t3.y = 0 AND t3.x = t1.y ; .print - explain query plan SELECT * FROM t1, t2, t3 WHERE t1.x IN (1,2,3,4) AND t1.y = t2.x AND t1.y IN (1,2,3,4) AND t3.y = 0 AND t3.x = t1.y ; This is not technically a bug, since it gets the right answer in all cases. It is just that sometimes the answer arrives more slowly than you would like. The problem stems from the two constraints on t1.y, and the fact that the query optimizer has to choose between them. On Fri, Apr 19, 2013 at 9:30 AM, Mario M. Westphal wrote: > > I'm currently uploading the database and will send you a link to it. > > -- Mario > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
I'm currently uploading the database and will send you a link to it. -- Mario ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
On Fri, Apr 19, 2013 at 9:17 AM, Mario M. Westphal wrote: > Hi, thanks. > > But I'm not sure that I understand you correctly. > > Is this behavior considered as something that needs to be looked at by the > SQLite Team to restore the original performance, or is this how "it is" now > and I have to find a work-around for good (e.g. applying your suggestion > with CROSS)? If you look into this, I stick with the older SQLite version > for now, which works perfectly for me. > I'm looking at it. The workaround is just that - a way to allow you to continue functioning until the issue is resolved. Sending me the database will help. > > -- Mario > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
Hi, thanks. But I'm not sure that I understand you correctly. Is this behavior considered as something that needs to be looked at by the SQLite Team to restore the original performance, or is this how "it is" now and I have to find a work-around for good (e.g. applying your suggestion with CROSS)? If you look into this, I stick with the older SQLite version for now, which works perfectly for me. -- Mario ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
On Thu, Apr 18, 2013 at 3:27 AM, Mario M. Westphal wrote: > We can provide a sample database etc. on request. > Can you send the database to my private email, please? -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
The change in behavior occurs with http://www.sqlite.org/src/info/38852f158a If you need a work-around, change INNER to CROSS in the queries and the old query plan will be restored. On Thu, Apr 18, 2013 at 3:27 AM, Mario M. Westphal wrote: > This is a SQL Script > > > /* > Application linking to SQLite using the Amalgation. > Build Tool: C++, Visual Studio 2012, Windows 7 64-Bit > > The table schema and the query enclosed below are in use > for over one year and various SQLite versions. > > After downloading and compiling in the SQLite 3.7.16.2, we have a problem. > > The INSERT statement which takes only a few seconds with previous builds > now does not return, at least not within several minutes. > It's hard to debug the amalgation in Visual Studio but it looks like > SQLite would be caught in an internal loop inside the step() function call. > > Replacing the latest version of SQLite with 3.7.15.1 (which was the one we > used before) > and re-compiling our application solves the problem. The INSERT works again > in a few seconds. > > We can provide a sample database etc. on request. > */ > > > /* Create */ > > CREATE VIRTUAL TABLE md_fts_core USING > fts4(group_oid,tag_oid,file_oid,lang,data); > > CREATE TABLE md_fts_core_tag (oid INTEGER PRIMARY KEY, type INTEGER); > CREATE INDEX idx_md_fts_core_tag_type ON md_fts_core_tag(type); > > CREATE TABLE md_tag (oid INTEGER PRIMARY KEY, class INTEGER, group_oid > INTEGER, id TEXT, tag TEXT, idx INTEGER, dtype INTEGER, ntype TEXT, cnt > INTEGER, repeat INTEGER, flags INTEGER, FOREIGN KEY(group_oid) REFERENCES > md_tag_group(oid) ON DELETE CASCADE); > CREATE INDEX idx_md_tag_tag ON md_tag(tag); > > CREATE TABLE md_tag_group (oid INTEGER PRIMARY KEY, src INTEGER, id TEXT); > > CREATE TABLE md_tag_data (oid INTEGER, tag_oid INTEGER, tdata TEXT, rdata > TEXT, lang TEXT, flags INTEGER, FOREIGN KEY(tag_oid) REFERENCES md_tag(oid) > ON DELETE CASCADE); > CREATE INDEX idx_md_tag_data_flags ON md_tag_data(flags); > CREATE INDEX idx_md_tag_data_oid ON md_tag_data(oid); > CREATE INDEX idx_md_tag_data_oid_tag_oid ON md_tag_data(oid,tag_oid); > CREATE INDEX idx_md_tag_data_tag_oid ON md_tag_data(tag_oid); > > > /* This insert does not return (at least not within several minutes in > 3.7.16.2, but takes about 5-10 seconds with build 3.7.15.1 */ > > INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data) > > SELECT > g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d > INNER JOIN > md_tag t ON d.tag_oid = t.oid > INNER JOIN > md_tag_group g ON t.group_oid = g.oid > INNER JOIN > md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid /* which > tags to include */ > WHERE > d.oid IN > > (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 > > 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 > > ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, > > 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107 > ,108) > AND d.tag_oid IN (2157,7309,16265,16579) > > UNION > > SELECT > /* We use group_concat to fold multiple values for one tag into one > value for FTS */ > g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ') > FROM > md_tag_data d > INNER JOIN > md_tag t ON d.tag_oid = t.oid > INNER JOIN > md_tag_group g ON t.group_oid = g.oid > INNER JOIN > md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid /* which > tags to include */ > WHERE > d.oid IN > > (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 > > 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 > > ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, > > 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107 > ,108) > AND d.tag_oid IN (2157,7309,16265,16579) > GROUP BY > d.tag_oid,d.oid > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
Hi, Celemens I created the requested log data by running the same operations in my application, once compiled with the 3.7.15.1 version (OK) and once with the 3.7.16.2 (BAD performance). The query plans are quite different. 1. --- The query my application runs for this test INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data) SELECT g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d INNER JOIN md_tag t ON d.tag_oid = t.oid INNER JOIN md_tag_group g ON t.group_oid = g.oid INNER JOIN md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid WHERE d.oid IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, 85,86,87,88,89,90,91,92,93,94,95,96,97,98) AND d.tag_oid IN (2157,7309,16265,16579) UNION SELECT g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ') FROM md_tag_data d INNER JOIN md_tag t ON d.tag_oid = t.oid INNER JOIN md_tag_group g ON t.group_oid = g.oid INNER JOIN md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid WHERE d.oid IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, 85,86,87,88,89,90,91,92,93,94,95,96,97,98) AND d.tag_oid IN (2157,7309,16265,16579) GROUP BY d.tag_oid,d.oid 2. --- Plan of 3.7.15.1 (GOOD) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~65 rows) EXECUTE LIST SUBQUERY 2 EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~65 rows) EXECUTE LIST SUBQUERY 3 EXECUTE LIST SUBQUERY 3 SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) USE TEMP B-TREE FOR GROUP BY COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) 3. --- Plan of 3.7.16.2 (BAD) SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~2 rows) EXECUTE LIST SUBQUERY 2 EXECUTE LIST SUBQUERY 2 SEARCH TABLE md_fts_core_tag AS ct USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 3 SEARCH TABLE md_tag AS t USING INTEGER PRIMARY KEY (rowid=?) (~4 rows) EXECUTE LIST SUBQUERY 3 SEARCH TABLE md_tag_group AS g USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) SEARCH TABLE md_tag_data AS d USING INDEX idx_md_tag_data_tag_oid (tag_oid=?) (~2 rows) EXECUTE LIST SUBQUERY 3 EXECUTE LIST SUBQUERY 3 USE TEMP B-TREE FOR GROUP BY COMPOUND SUBQUERIES 1 AND 2 USING TEMP B-TREE (UNION) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Potential problem in SQLite 3.7.16.2
Mario M. Westphal wrote: > The INSERT statement which takes only a few seconds with previous builds > now does not return, at least not within several minutes. Please show the output of EXPLAIN QUERY PLAN for the SELECT part of the query in both versions. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Potential problem in SQLite 3.7.16.2
This is a SQL Script /* Application linking to SQLite using the Amalgation. Build Tool: C++, Visual Studio 2012, Windows 7 64-Bit The table schema and the query enclosed below are in use for over one year and various SQLite versions. After downloading and compiling in the SQLite 3.7.16.2, we have a problem. The INSERT statement which takes only a few seconds with previous builds now does not return, at least not within several minutes. It's hard to debug the amalgation in Visual Studio but it looks like SQLite would be caught in an internal loop inside the step() function call. Replacing the latest version of SQLite with 3.7.15.1 (which was the one we used before) and re-compiling our application solves the problem. The INSERT works again in a few seconds. We can provide a sample database etc. on request. */ /* Create */ CREATE VIRTUAL TABLE md_fts_core USING fts4(group_oid,tag_oid,file_oid,lang,data); CREATE TABLE md_fts_core_tag (oid INTEGER PRIMARY KEY, type INTEGER); CREATE INDEX idx_md_fts_core_tag_type ON md_fts_core_tag(type); CREATE TABLE md_tag (oid INTEGER PRIMARY KEY, class INTEGER, group_oid INTEGER, id TEXT, tag TEXT, idx INTEGER, dtype INTEGER, ntype TEXT, cnt INTEGER, repeat INTEGER, flags INTEGER, FOREIGN KEY(group_oid) REFERENCES md_tag_group(oid) ON DELETE CASCADE); CREATE INDEX idx_md_tag_tag ON md_tag(tag); CREATE TABLE md_tag_group (oid INTEGER PRIMARY KEY, src INTEGER, id TEXT); CREATE TABLE md_tag_data (oid INTEGER, tag_oid INTEGER, tdata TEXT, rdata TEXT, lang TEXT, flags INTEGER, FOREIGN KEY(tag_oid) REFERENCES md_tag(oid) ON DELETE CASCADE); CREATE INDEX idx_md_tag_data_flags ON md_tag_data(flags); CREATE INDEX idx_md_tag_data_oid ON md_tag_data(oid); CREATE INDEX idx_md_tag_data_oid_tag_oid ON md_tag_data(oid,tag_oid); CREATE INDEX idx_md_tag_data_tag_oid ON md_tag_data(tag_oid); /* This insert does not return (at least not within several minutes in 3.7.16.2, but takes about 5-10 seconds with build 3.7.15.1 */ INSERT INTO md_fts_core (group_oid,tag_oid,file_oid,lang,data) SELECT g.oid, d.tag_oid, d.oid, d.lang, d.tdata from md_tag_data d INNER JOIN md_tag t ON d.tag_oid = t.oid INNER JOIN md_tag_group g ON t.group_oid = g.oid INNER JOIN md_fts_core_tag ct ON ct.type = 0 AND ct.oid = d.tag_oid /* which tags to include */ WHERE d.oid IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107 ,108) AND d.tag_oid IN (2157,7309,16265,16579) UNION SELECT /* We use group_concat to fold multiple values for one tag into one value for FTS */ g.oid, d.tag_oid, d.oid, d.lang, group_concat(d.tdata,' ') FROM md_tag_data d INNER JOIN md_tag t ON d.tag_oid = t.oid INNER JOIN md_tag_group g ON t.group_oid = g.oid INNER JOIN md_fts_core_tag ct ON ct.type = 1 AND ct.oid = d.tag_oid /* which tags to include */ WHERE d.oid IN (1,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,3 4,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59 ,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84, 85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107 ,108) AND d.tag_oid IN (2157,7309,16265,16579) GROUP BY d.tag_oid,d.oid ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users