[sqlite] SQLite take lower performance while using shared cache on iOS/Mac

[sanhua.zh]

I try to use shared cache to optimize my code. Sincesqlite3_enable_shared_cache 
is deprecated on iOS/Mac, I usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag 
to open shared cache mode.
4 threads select is running in my code, while each thread has its own sqlite 
connection and do the same thing - select all 100000 item from ?test? table.
Strange thing happened.
Each thread ends up within 0.09 seconds without SQLITE_OPEN_SHAREDCACHE, but 
with SQLITE_OPEN_SHAREDCACHE ends up in 14 second, which is much slower.
As sqlite.org said, shared cache mode can reduce the memory and IO, which leads 
to better performance.
Any one can tell me whether I write the wrong code or using shared cache mode 
in an incorrect scene ?


Here is my code mixed by C and Objective-C, but it will not stop your reading 
and understanding:


#import Foundation/Foundation.h
#import sqlite3.h
#import sys/time.h


double now()
{
  timeval now;
  gettimeofday(now, nullptr);
  return now.tv_sec+now.tv_usec/1000000.0;
}


void showResultCode(int resultCode)
{
  if (resultCode!=SQLITE_DONEresultCode!=SQLITE_OKresultCode!=SQLITE_ROW) {
    NSLog(@"unexperted result %d", resultCode);
  }
}


void SQLiteLog(void* userInfo, int ret, const char* msg)
{
  NSLog(@"ret=%d, msg=%s", ret, msg);
}


void write(const char* path)
{
  sqlite3* handle;


  showResultCode(sqlite3_open(path, handle));


  showResultCode(sqlite3_exec(handle, "PRAGMA synchronous=FULL", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "PRAGMA journal_mode=WAL", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "drop table if exists test;", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "create table if not exists test(id 
integer);", nullptr, nullptr, nullptr));


  sqlite3_stmt* stmt = nullptr;
  showResultCode(sqlite3_exec(handle, "BEGIN", nullptr, nullptr, nullptr));
  for (int i = 0; i  1000000; i++) {
    showResultCode(sqlite3_prepare(handle, [NSString stringWithFormat:@"insert 
into test values(%d);", i].UTF8String, -1, stmt, nullptr));
    showResultCode(sqlite3_step(stmt));
    showResultCode(sqlite3_finalize(stmt));
  }
  showResultCode(sqlite3_exec(handle, "COMMIT", nullptr, nullptr, nullptr));
  showResultCode(sqlite3_close(handle));
}




void read(const char* path)
{
  sqlite3* handle;


  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_SHAREDCACHE, nullptr));
//  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE, 
nullptr));
  sqlite3_stmt* stmt;
  showResultCode(sqlite3_prepare(handle, "select * from test;", -1, stmt, 
nullptr));


  double start = now();
  int integer = 0;
  while (sqlite3_step(stmt)!=SQLITE_DONE) {
    integer = sqlite3_column_int(stmt, 0);
  }
  NSLog(@"%d", integer);
  showResultCode(sqlite3_finalize(stmt));
  double end = now();
  NSLog(@"cost %f", end-start);


  showResultCode(sqlite3_close(handle));
}


int main(int argc, char * argv[])
{
  sqlite3_config(SQLITE_CONFIG_LOG, SQLiteLog, NULL);
  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);


  const char* path = "/Users/sanhuazhang/Desktop/test.db";


  write(path);
  for (int i = 0; i  4; i++) {
    dispatch_queue_t queue = dispatch_queue_create([NSString 
stringWithFormat:@"queue%d", i].UTF8String, DISPATCH_QUEUE_CONCURRENT);
    dispatch_async(queue, ^{
      read(path);
    });
  }


  sleep(10000);
  return 1;
}