Re: [sqlite] Severe performance degradation between 3.8.4.3 and
On Fri, 22 Aug 2014 19:14:02 +0200 "Mario M. Westphal" wrote: > Thanks, Richard > > > > After swapping back to the latest SQLite version and running an > Analyze on the sample databases, performance is up to the same level > as before (maybe even a bit faster). Very good. > > > > I will send out a recommendation to my users to run the weekly > diagnostics routine immediately to restore performance. Next time you can do it automatically. Set pragma user_version on db to your app version, and on open db check if it's current or not, and run analyze or make schema changes or whatever you want and update user_version. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Severe performance degradation between 3.8.4.3 and
Thanks, Richard After swapping back to the latest SQLite version and running an Analyze on the sample databases, performance is up to the same level as before (maybe even a bit faster). Very good. I will send out a recommendation to my users to run the weekly diagnostics routine immediately to restore performance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Severe performance degradation between 3.8.4.3 and
On Thu, Aug 21, 2014 at 2:15 PM, Mario M. Westphal wrote: > > When I understand you correctly, I should/must run an ANALYSIS on existing > databases > SQLite will get the correct answer regardless. But you might get the answer *faster* if you run ANALYZE after significant changes to the database. But you should always get an equivalent answer. Additional discussion can be found at: http://www.sqlite.org/optoverview.html http://www.sqlite.org/queryplanner-ng.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] Severe performance degradation between 3.8.4.3 and
The sample database was produced by a version of my software which runs the previous (or even an older version of SQLite). My software runs an Analysis as part of a weekly database maintenance procedure. But the users can turn this off or delay it for weeks. Various versions of my software are in use, and each version links against a different version of SQLite. Not all users keep up with upgrades, or skip some of the monthly updates... When I understand you correctly, I should/must run an ANALYSIS on existing databases after shipping a new version of SQLite with my application? In case the statistics data in existing databases causes the updated optimizer to choose slower execution paths? This can be arranged. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Severe performance degradation between 3.8.4.3 and
On Thu, Aug 21, 2014 at 7:35 AM, Mario M. Westphal wrote: > Hi, Richard > > I have prepared a sample database, sample statements and some additional > details and sent it to your email address. > Thanks for sending the sample data. Here is what I found: Your ANALYZE information (stored in the sqlite_stat1 and sqlite_stat3 tables) is out-of-date and no longer reflects the shape of the actual data in the database. You can fix this in either of two ways: (1) Rerun ANALYZE (2) Remove the analysis using: "DROP TABLE sqlite_stat1; DROP TABLE sqlite_stat3;" If you do either of the above, the second query is fast again. I don't know about the first query because it depends on the "_temptable" table which is not a part of the package you sent, so I am unable to run it. -- 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] Severe performance degradation between 3.8.4.3 and
On Thu, Aug 21, 2014 at 7:35 AM, Mario M. Westphal wrote: > Hi, Richard > > I have prepared a sample database, sample statements and some additional > details and sent it to your email address. > Thanks for the info. I'll look into the performance regression as soon as I get a chance. Right now we have a more pressing problem to deal with: www.sqlite.org/src/tktview/369d57fb8e5c - sorry for the delay. -- 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] Severe performance degradation between 3.8.4.3 and
Hi, Richard I have prepared a sample database, sample statements and some additional details and sent it to your email address. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Severe performance degradation between 3.8.4.3 and
On Thu, Aug 21, 2014 at 5:18 AM, Richard Hipp wrote: > > > > On Thu, Aug 21, 2014 at 3:21 AM, Mario M. Westphal wrote: > >> Hi, >> >> >> >> Information provided as requested. >> > > But not in a form that we can use. > > Please bring up your database file in a new 3.8.6 sqlite3.exe shell and > type ".fullschema" and then post the output. You might want to make use of > the ".once" command too: > > .once out.txt > .fullschema > > The above will put the needed information in the file out.txt. Exit the > shell. Type "start out.txt". Then scrap the text and post it here. > Or, if you wish, you can send me the complete database file as an attachment in a private email. > > Thanks. > > > -- > D. Richard Hipp > d...@sqlite.org > -- 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] Severe performance degradation between 3.8.4.3 and
Mario M. Westphal wrote: > _temptable is a temporary table which contains a list of oids (integer, ~ 10 > rows) to consider. The information that oid is INTEGER PRIMARY KEY would have been helpful ... > For query 2.1 > > selectid order from detail > 1 0 0 SCAN TABLE stack_elem AS e USING > INDEX idx_stack_elem_oid > ... Slow or fast? And the output for the other version is needed, too. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Severe performance degradation between 3.8.4.3 and
On Thu, Aug 21, 2014 at 3:21 AM, Mario M. Westphal wrote: > Hi, > > > > Information provided as requested. > But not in a form that we can use. Please bring up your database file in a new 3.8.6 sqlite3.exe shell and type ".fullschema" and then post the output. You might want to make use of the ".once" command too: .once out.txt .fullschema The above will put the needed information in the file out.txt. Exit the shell. Type "start out.txt". Then scrap the text and post it here. Thanks. -- 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] Severe performance degradation between 3.8.4.3 and
Hi, Information provided as requested. _temptable is a temporary table which contains a list of oids (integer, ~ 10 rows) to consider. Stats3 tbl idx neqnlt ndltsample stack_elem idx_rel_stack_elem_soid 4 0 0 406 stack_elem idx_rel_stack_elem_oid 1 0 0 4 stack_elem idx_rel_stack_elem_oid 1 1 1 5 stack_elem idx_rel_stack_elem_oid 1 2 2 6 stack_elem idx_rel_stack_elem_oid 1 3 3 133 stack_elem idx_rel_stack_elem_soid_oid4 0 0 406 Stats2 tbl idx stat stack idx_rel_stack_toid_rtype 210 1 1 stack_elem idx_rel_stack_elem_soid 4 4 stack_elem idx_rel_stack_elem_oid 4 1 stack_elem idx_rel_stack_elem_soid_oid4 4 1 For query 2.1 selectid order from detail 1 0 0 SCAN TABLE stack_elem AS e USING INDEX idx_stack_elem_oid 1 1 1 SEARCH TABLE stack AS s USING INTEGER PRIMARY KEY (rowid=?) 1 2 2 SEARCH TABLE _temptable AS _t USING INTEGER PRIMARY KEY (rowid=?) 1 3 3 SEARCH TABLE _temptable AS _t2 USING INTEGER PRIMARY KEY (rowid=?) 2 0 1 SCAN TABLE stack AS s 2 1 2 SEARCH TABLE _temptable AS _t USING INTEGER PRIMARY KEY (rowid=?) 2 2 0 SEARCH TABLE rel_rel AS r USING COVERING INDEX idx_rel_rel (moid=?) 2 0 0 USE TEMP B-TREE FOR DISTINCT 2 0 0 USE TEMP B-TREE FOR ORDER BY 0 0 0 COMPOUND SUBQUERIES 1 AND 2 (UNION) For query 2.2 selectid order from detail 0 0 0 SEARCH TABLE stack USING INTEGER PRIMARY KEY (rowid=?) 0 0 0 EXECUTE LIST SUBQUERY 0 0 0 0 SCAN TABLE stack USING COVERING INDEX idx_stack_toid_rtype 0 1 1 SEARCH TABLE stack_elem USING COVERING INDEX idx_stack_elem_soid_oid (soid=?) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Severe performance degradation between 3.8.4.3 and 3.8.6
Mario M. Westphal wrote: > The new version is 10 or more times slower than the previous build I used > (3.8.4.3). > [...] > If more information or sample data is needed, let me know. What is _temptable? If you have run ANALZYE, what are the contents of the sqlite_stat* tables? What is the EXPLAIN QUERY PLAN output in both versions? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
