Re: [sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?
Thank you for your advice Simon and Keith. We strive to make the migration process faster, because our technicians are responsible of changing the program version of each embedded device and then migrating its database, not the end user. I'm creating the indexes last as you said, but haven't used the analyze command. I will try it. I found the problem to be in the way I'm compiling the source code. A forgotten option "-fsanitize=address" while compiling caused this slow down. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I increase the performance of sqlite3_exec() or maybe change it to prepare+step?
Hello, I am using sqlite C API to migrate a database. Migration consists of many SQL statements that are known in advance. To migrate a DB from version 3 to version 7 the C program does the following: 1. disable foreign_keys (PRAGMA foreign_keys = OFF); 2. open transaction (BEGIN TRANSACTION); 3. execute bunch of statements that migrates the DB to the next version using *sqlite3_exec(db, migrate[version], NULL, NULL, )*; migrate[version] is consisting of many (sometimes several thousand) statements; 4. check for foreign_keys inconsistencies (PRAGMA foreign_key_check); 5. commit transaction (COMMIT TRANSACTION); 6. enable foreign_keys again (PRAGMA foreign_keys = ON); 7. vacuums db file (vacuum); I've realized that using the command line tool the migration takes around 8 minutes, but the C program takes around 20 minutes. This time is consumed in point number 3 in the previous list. How can I increase the performance of my program so that it reaches the performance of the command line tool? My first bet is to prepare each individual statement and then execute it. Should it be faster than sqlite3_exec? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how is "pragma threads = 4" working
On 08/15/2016 12:02 AM, Венцислав Русев wrote: My computer has 4 cores. I have compile sqlite like this "gcc -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4 shell.c sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found that "pragma threads = 4" doesn't decrease runtime of the query that sorts 1 milion records. SQLite version 3.8.8 sqlite> pragma threads; 4 sqlite> CREATE TABLE event ( ID INTEGER PRIMARY KEY NOT NULL, date INTEGER NOT NULL, value INTEGER NOT NULL ); sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1; 0|0|0|SCAN TABLE event 0|0|0|USE TEMP B-TREE FOR ORDER BY sqlite> SELECT ID FROM event ORDER BY date LIMIT 1; 4101021 Run Time: real 2.493 user 2.426000 sys 0.049000 sqlite> pragma threads = 0; 0 sqlite> SELECT ID FROM event ORDER BY date LIMIT 1; 4101021 Run Time: real 2.484 user 2.421000 sys 0.044000 To sum it up I have: - multi core cpu; - sqlite that is compiled to use "auxiliary threads"; - a table with 1 million records; - a query that scans through the table, sorts all records and outputs the first ID. sqlite doesn't use these "auxiliary threads" that sqlite docs talks about and the runtime of that query is the same with or without "pragma threads = 4". ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users Some clarifications: - OS: Ubuntu 14.04 LTS; - "PRAGMA compile_options" gives "SYSTEM_MALLOC THREADSAFE=1"; - when sqlite shell is running and no query is running at the moment the OS reports that it has only one thread; On 09/16/2016 11:29 AM, Richard Hipp wrote: The algorithm used for "ORDER BY ... LIMIT N" uses much less memory than a full-up "ORDER BY" because is only keeps track of the top N entries seen so far, discarding the rest. But it also only uses a single thread. If you want multiple threads to be used, you'll need to drop the LIMIT, though I imagine that would defeat your purpose, no? Richard Hipp has a point. I searched the sources and found that the sorting algorithm uses threads only if there isn't a LIMIT clause. To be exact the sources say "This file contains code for the VdbeSorter object, used in concert with a VdbeCursor to sort large numbers of keys for CREATE INDEX statements or by SELECT statements with ORDER BY clauses that cannot be satisfied using indexes and without LIMIT clauses." But I'm wondering what if your query is like so "SELECT ID FROM event ORDER BY date LIMIT 1 OFFSET 100;"? Isn't it better to sort the results using available threads? On 09/16/2016 12:13 PM, Adam Devita wrote: Have you proven that the cpu is the bottleneck? Sorting a million rows seems like a lot, but even older single core cpus may be capable of 2 billion ops per second. [I apologize if this has been sorted out already I've got about 2 days of history on the thread] regards, Adam DeVita This was just a test to see the performance of "PRAGMA threads". Actually I expect to sort 10-30 million rows or more. But after the tests I may choose the index. Just to let you know what other tests I've conducted. sqlite> .output stdout sqlite> pragma threads = 4; sqlite> select ID from event order by date; Run Time: real 2.880 user 4.469000 sys 0.147000 sqlite> pragma threads = 0; sqlite> select ID from event order by date; Run Time: real 3.870 user 3.771000 sys 0.075000 Besides the runtime difference I inspected the process information while the query is running. When "pragma threads = 4;" sqlite shell starts new threads for sorting and stops them after the query has finished. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] how is "pragma threads = 4" working
My computer has 4 cores. I have compile sqlite like this "gcc -DSQLITE_MAX_WORKER_THREADS=4 -DSQLITE_DEFAULT_WORKER_THREADS=4 shell.c sqlite3.c -lpthread -ldl -o sqlite3". I made some tests and found that "pragma threads = 4" doesn't decrease runtime of the query that sorts 1 milion records. SQLite version 3.8.8 sqlite> pragma threads; 4 sqlite> CREATE TABLE event ( ID INTEGER PRIMARY KEY NOT NULL, date INTEGER NOT NULL, value INTEGER NOT NULL ); sqlite> EXPLAIN QUERY PLAN SELECT ID FROM event ORDER BY date LIMIT 1; 0|0|0|SCAN TABLE event 0|0|0|USE TEMP B-TREE FOR ORDER BY sqlite> SELECT ID FROM event ORDER BY date LIMIT 1; 4101021 Run Time: real 2.493 user 2.426000 sys 0.049000 sqlite> pragma threads = 0; 0 sqlite> SELECT ID FROM event ORDER BY date LIMIT 1; 4101021 Run Time: real 2.484 user 2.421000 sys 0.044000 To sum it up I have: - multi core cpu; - sqlite that is compiled to use "auxiliary threads"; - a table with 1 million records; - a query that scans through the table, sorts all records and outputs the first ID. sqlite doesn't use these "auxiliary threads" that sqlite docs talks about and the runtime of that query is the same with or without "pragma threads = 4". ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite - Delete large table in sqlite
On 09/09/2016 12:49 PM, Bhavesh Patel wrote: I have a 4GB SQLite database (currently using SQLCipher for encryption). I have created a physical table in the SQLite DB which has approx 20 rows x 183 columns. When I am trying to delete the table. Sqlite is taking up a lot of time to delete the huge table. I tried drop table and also delete all the rows - in both the cases it takes up 1-2 minutes approx. during the process of delete/drop is running, if I try to connect I randomly get database lock error. I would like to know if there is any optimized way to delete such huge table or any other ways to run drop/delete but allow other users to connect and use the database or can avoid waiting of other connect to the DB? Thanks in advance ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users I don't know if this will work for you, but can't you delete 1000 rows then another 1000 ... until the table is empty. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
