SQLite really slow

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 and that's why I'm asking for your help.

I've watch the threads in SO and followed some of the advices I found through that journey. So... cutting to the chase...

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 list)
  }
}
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?