SQLite really slow

Question

I've been struggling to get sqlite much faster in my C++ program. I believe that the results are far from what it was supposed to be.

I have a few tables in the database, most of them with a few records and one with a real big number of records (4986450). It was really hard to get to this size because the inserts were too many per transaction and because it was an slow insert.

On the other hand, now I'm making a simple query on that big table such as

sqlite3_prepare_v2(db,"SELECT * FROM Table where primary_key=?1;",-1, &query,NULL);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
....
while(running){
   sqlite3_bind_text(query, 1, pkey.c_str(), (int)pkey.size() , SQLITE_STATIC);

  int query_status = sqlite3_step(query);
  if(query_status ==  SQLITE_ROW){
      data = sqlite3_column_int(query,1);
      (... just saving data in a map)
  }
}
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

(I've change the names of the table and column just for simplicity). This query is in a while cicle, and is done lots of times in the same transaction. It takes around 9 seconds to the select query 500 times. Even when I was inserting the data into the table, I could get better times.

I have the following pragmas on the database

PRAGMA main.page_size = 4096;
PRAGMA main.cache_size=10000;
PRAGMA main.locking_mode=EXCLUSIVE;
PRAGMA main.synchronous=OFF;
PRAGMA main.journal_mode=WAL;
PRAGMA main.cache_size=5000;

Can you help me tunning the database? What am I doing wrong?

Out of curiosity, how's the performance when `(... just saving data in a list)` is *entirely* commented out? — WhozCraig, Jun 26 '15 at 14:12
Probably of interest to you: [those](http://stackoverflow.com/a/2929821/2302862) [two](http://stackoverflow.com/a/8888410/2302862) answers. — Siguza, Jun 26 '15 at 14:14
@WhozCraig I'm sorry I meant map. It takes almost the same time. — anatp2015, Jun 26 '15 at 14:33
@Siguza Thank you for the posts. I've applied most of the suggestions and still the same result :/ — anatp2015, Jun 26 '15 at 15:20
Are you really sure that that column is the primary key? Show the output of [EXPLAIN QUERY PLAN](http://www.sqlite.org/eqp.html). — CL., Jun 29 '15 at 13:00
Try to use auto_vacuum=true, too, in order to shrink the db (if some deletes are done). Have you tried to double your page_size? I see that you have repeated cache_size... this is a mistake? — aprados, Nov 03 '15 at 11:13

score 0 · Answer 1 · answered Nov 11 '15 at 21:13

Option number one: Is there no possibility to have integer primary key instead of string - it really kills the SELECT performance.

Option number two: How many columns has the table and do you need all of them in your recordset? If you can spare reading and copying of data, you may get better results.

Option number three: Is there no possibility to rewrite the loop to be controlled by the sqlite3_step instead of artificial outer control? Iteration over recordset (with occassional skipping of unused rows) is tons better than reselecting new recordset each time. Saving data into a map suggests that this should be possible.

SQLite really slow

1 Answers1