>> Any other chance to speed this up (apart from the obvious "optimize the >> query, do not use distinct on large tables....)= > > Without seeing the query or database schema? Not really... Depending > on the exact query an index on "xyz" might help.
Another suggestion could be to turn off shared cache mode. Pavel On Fri, May 6, 2011 at 7:15 AM, Dan Kennedy <danielk1...@gmail.com> wrote: > On 05/06/2011 05:53 PM, Ole Reinhardt wrote: >> Hi Dan, >> >>>> I have to correct myself a little... the hanging sqlite3_open_v2() does >>>> not happe while the prepare of the first query but during the first >>>> sqlite3_step() of the first query... >>> >>> Each shared-cache has a mutex associated with it. When >>> sqlite3_step is called it grabs the mutexes for all shared-caches >>> it might use. The mutexes are not released until the call >>> to sqlite3_step() returns. >>> >>> So if you have one query that spends a lot of time in sqlite3_step() >>> you are going to block your other threads. Unfortunately, that is >>> the nature of shared-cache mode. >> >> Thanks for the info. But is this even true when enabling read >> uncommitted isolation mode using: >> >> sqlite3_exec(log_database_local, "PRAGMA read_uncommitted=True;", NULL, >> 0,&err) >> >> ?? >> >> I suspect it's the master table lock that is hold there, right? > > You are correct that your query will hold a shared-lock on the > master table at the shared cache level, but it's the mutex that > is causing you problems. Shared-cache locks are held for the > duration of a transaction to ensure transactions are correctly > isolated. In this case the master table is locked to make sure > that the table your query is accessing is not dropped by another > thread while you are using it. > > Mutexes are held for the duration of an sqlite3_step() call to make > sure SQLite doesn't segfault when two threads try to access the same > shared-cache object. > >> Any other chance to speed this up (apart from the obvious "optimize the >> query, do not use distinct on large tables....)= > > Without seeing the query or database schema? Not really... Depending > on the exact query an index on "xyz" might help. > > Dan. > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users