Using a shared cache will result in lower memory usage and may result in lower
IO, but it isn't likely to speed up your program since it also requires more
locks to guarantee safety between competing database connections.
SQLite on Apple's platforms is built with SQLITE_THREADSAFE=2, so under normal
use there is minimal locking overhead.
Unless you absolutely need to conserve every last byte of memory?and since
you're writing Objective-C that's almost certainly not the case?you should not
be using a shared cache.
On Dec 16, 2015, at 10:58 PM, sanhua.zh <sanhua.zh at foxmail.com> wrote:
>
> 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;
> }
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
