[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
Nicolas there are other people who also receive this mail. if you have code that will help someone else. Please post the information Mr Hipp requested. Jay On Tue, Apr 28, 2015 at 5:08 PM, Nicolas Boullis wrote: > Hi, > > On Mon, Apr 20, 2015 at 06:25:21AM -0400, Richard Hipp wrote: > > > > Please send us: > > > > (1) The output of "SELECT sqlite_source_id();" > > > > (2) The complete text of you SELECT statement. > > > > (3) The output of the ".fullschema" command from the most recent > > version of the sqlite3.exe command-line shell run on your database > > file after it is ANALYZE-ed. > > Sorry for the very late answer, I?ve been very busy lately. > > I investigated a little further, measuring the time taken by each SELECT > request that was run, and then using EXPLAIN QUERY PLAN to understand > why two of them were so slow. Knowing the data, I added a few indices, > and now the whole program runs in about 1 second, much better than what > I had before. > > As far as I am concerned, everything is fine, now. Many thanks for your > help. > > Now, if you are realy interested by that surprisingly-good performance I > had without indices and without running ANALYZE, I may try to provide > you with more details. > > > Thanks to all, > > -- > Nicolas Boullis > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
Hi, On Mon, Apr 20, 2015 at 06:25:21AM -0400, Richard Hipp wrote: > > Please send us: > > (1) The output of "SELECT sqlite_source_id();" > > (2) The complete text of you SELECT statement. > > (3) The output of the ".fullschema" command from the most recent > version of the sqlite3.exe command-line shell run on your database > file after it is ANALYZE-ed. Sorry for the very late answer, I?ve been very busy lately. I investigated a little further, measuring the time taken by each SELECT request that was run, and then using EXPLAIN QUERY PLAN to understand why two of them were so slow. Knowing the data, I added a few indices, and now the whole program runs in about 1 second, much better than what I had before. As far as I am concerned, everything is fine, now. Many thanks for your help. Now, if you are realy interested by that surprisingly-good performance I had without indices and without running ANALYZE, I may try to provide you with more details. Thanks to all, -- Nicolas Boullis
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
On Mon, 20 Apr 2015 12:01:59 +0200 Nicolas Boullis wrote: > Hi, > > On Mon, Apr 20, 2015 at 11:18:56AM +0200, Clemens Ladisch wrote: > > If that does not help, we'll have to look at the actual queries (and > > their EXPLAIN QUERY PLAN output). > > That would certainly help, but I would have to ask for permission to > make this information public, or to anonymize even the names of the > tables and columns. Knowing: a) the kind of data (do you use blobs or big text columns?) b) if data was inserted on primary key sort order or randomized c) if you have a multi-gigabyte db d) pragmas configuration e) column order (put blobs and text at the end of table column) may point us to help you more without exposing internal information. Also remember to don't use primary key on multicolumn indexes and upgrade your sqlite3 to newer version (from 3.7.13 to 3.8.9) > > Thanks for your help, > > -- > Nicolas Boullis > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users --- --- Eduardo Morras
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
Hi, On Mon, Apr 20, 2015 at 11:18:56AM +0200, Clemens Ladisch wrote: > > SQLite does not keep statistics automatically. Run ANALYZE. Thanks for the explanation. I just performed some measurements. When I use the database with PRIMARY KEY/UNIQUE constraints, the program uses 540s of CPU time before ANALYZE, and 17.5s of CPU time after ANALYZE. Much better! But if I use the database withouth PRIMARY KEY/UNIQUE constraints, it uses 5.7s of CPU time before ANALYZE, and 86s of CPU time after ANALYZE? The performance with PRIMARY KEY/UNIQUE constraints, after ANALYZE, is decent enough, but it?s still significantly slower than the database without the constraints before ANALYZE, which looks odd to me. > If that does not help, we'll have to look at the actual queries (and > their EXPLAIN QUERY PLAN output). That would certainly help, but I would have to ask for permission to make this information public, or to anonymize even the names of the tables and columns. Thanks for your help, -- Nicolas Boullis
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
Nicolas Boullis wrote: > I have a program that does mostly? SELECT requests, and it is very > slow. But I then figured out that, if I rebuild my SQLite database > without PRIMARY KEY/UNIQUE constraints, the program runs much faster > (no measurement yet, but I?d say at least 10? faster). SQLite does not keep statistics automatically. Run ANALYZE. If that does not help, we'll have to look at the actual queries (and their EXPLAIN QUERY PLAN output). Regards, Clemens
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
Hi, Disclaimer: I am a PostgreSQL user and consider myself a SQLite newbie. I have a program that does mostly? SELECT requests, and it is very slow. But I then figured out that, if I rebuild my SQLite database without PRIMARY KEY/UNIQUE constraints, the program runs much faster (no measurement yet, but I?d say at least 10? faster). As I understand it, SQLite builds implicit indexes for PRIMARY KEY/UNIQUE constraints, but I would not expect those indexes to significantly decrease the performance of SELECT requests? For what it?s worth, my program is written in Python 3, and run on an up-to-date Debian Wheezy system, with Python 3.2.3-7, dynamically linked with libsqlite3 3.7.13-1+deb7u1. Is there anything well-known that explains this performance difference? Thanks for your help, -- Nicolas Boullis Footnote: ? The program first creates and fills 2 temporary tables, with locally-gathered data, and then only performs SELECT queries with both the 2 temporary tables and the permanent tables. Nothing is ever written to the permanent tables, and the temporary tables are created with no contraint.
[sqlite] SELECT performance vs PRIMARY KEY/UNIQUE constraints
On 4/20/15, Nicolas Boullis wrote: > Hi, > > On Mon, Apr 20, 2015 at 11:18:56AM +0200, Clemens Ladisch wrote: >> >> SQLite does not keep statistics automatically. Run ANALYZE. > > Thanks for the explanation. I just performed some measurements. > > When I use the database with PRIMARY KEY/UNIQUE constraints, the program > uses 540s of CPU time before ANALYZE, and 17.5s of CPU time after > ANALYZE. Much better! > > But if I use the database withouth PRIMARY KEY/UNIQUE constraints, it > uses 5.7s of CPU time before ANALYZE, and 86s of CPU time after ANALYZE? > Please send us: (1) The output of "SELECT sqlite_source_id();" (2) The complete text of you SELECT statement. (3) The output of the ".fullschema" command from the most recent version of the sqlite3.exe command-line shell run on your database file after it is ANALYZE-ed. -- D. Richard Hipp drh at sqlite.org
