Here is the test result for selecting 100,000 items in original test case.
shared cache mode
2015-12-20 21:24:58.714 Test[1126:11609] cost 2.173480
2015-12-20 21:24:58.714 Test[1126:11610] cost 2.173449
2015-12-20 21:24:58.714 Test[1126:11608] cost 2.173768
2015-12-20 21:24:58.714 Test[1126:11611] cost 2.173169
without shared cache mode
2015-12-20 21:28:49.647 Test[1286:13077] cost 0.028914
2015-12-20 21:28:49.647 Test[1286:13078] cost 0.028914
2015-12-20 21:28:49.647 Test[1286:13079] cost 0.028964
2015-12-20 21:28:49.647 Test[1286:13076] cost 0.028958
May be your python code does not run the same thing as mine, I am poor in
python so that I could not figure it out.
????
???:E.Pasmapasma10 at concepts.nl
???:SQLite mailing listsqlite-users at mailinglists.sqlite.org
????:2015?12?18?(??)?18:29
??:Re: [sqlite] {Spam?} SQLite take lower performance while usingshared cache
on iOS/Mac
17 dec 2015, 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.
.... for (int i = 0; i 1000000; i++) { .."insert into test values(%d);"..
... Hello, A rude question: is there any chance that the test in shared cache
mode was taken with 1.000.000 rows instead of 100.000? Because I have quite
different results: regular mode: 6.2 seconds cache sharing: 6.35 seconds Thanks
for posting the program source. I had to use a Python program by lack of
objective C. I'll include the source too, see below. Another thing: I found
that sqlite3_open takes significant time when connecting to a shared cache that
is in use. Therefore the Python test measures the overall elapsed time. Python
offers a thread.join method to know exactly when a thread is finished. Tnanks,
E.Pasma import random, os, sys, time, threading, subprocess, socket, socket
import sqlite3 as sqlite TESTDB='larry.db' SHARED=0 SIZE=2000 print('cache
sharing', SHARED, 'cache size', SIZE) sqlite.enable_shared_cache(SHARED) def
connect(): con= sqlite.Connection (TESTDB, isolation_level=None,
check_same_thread=0) con.execute ("pragma cache_size=%i"%(SIZE,)) return con
def e_str (e): " format exception as string " return "%s: %s" %
(e.__class__.__name__, e) class Testthread (threading.Thread): """ execute
query in a thread """ def __init__ (self, qq, con = None): self.con = con
self.qq = qq self.out = "thr%i.out" % id (self) open (self.out, 'w').close ()
os.remove (self.out) threading.Thread.__init__ ( self, target=self.__target, )
def start (self): threading.Thread.start (self) for retry in range (10):
time.sleep (2 ** retry * .05) if os.access (self.out, os.R_OK): break else:
print("Testthread: spoolfile does not appear") time.sleep (.10) # for the SQL
to start def __target (self): if not self.con: self.con = connect () f = open
(self.out, 'w') try: try: for q in self.qq.split (';'): for i in
self.con.execute(q).fetchall(): f.write (str(i)+'\n') except Exception as e:
f.write (e_str (e) + '\n') finally: f.close() self.con.close () def join (self,
timeout=None): if timeout is not None: threading.Thread.join (self, timeout)
else: timeout = 7.5 # respond to keyboard interrupts while self.isAlive ():
threading.Thread.join (self, timeout) return self def get_result (self): try:
return open (self.out, 'r').read ().strip () except IOError as e: return None
def main (): # create some test data ###try: os.remove (TESTDB) ###except
OSError: pass t0=time.time() con = connect () cur = con.cursor () cur.execute
("begin") try: cur.execute ("create table test (id integer)") except
sqlite.OperationalError: pass else: for i in range (1000000): cur.execute
("insert into test(id) values(:1)",(i,)) cur.execute ("end") cur.close ()
con.close () print("na insert", time.time()-t0) qq="SELECT COUNT(*) FROM test
WHERE +id-1" qq+=';'+qq tthh=[] for i in range(4): con = None ###connect()
tthh.append (Testthread (qq, con)) print("na splits", time.time()-t0) for th in
tthh: th.start () print("na start", time.time()-t0) for th in tthh: res=th.join
().get_result().split('\n') assert res[0]=='(1000000,)', res[0]
print(time.time()-t0, 'seconds') if __name__ == "__main__": main ()
_______________________________________________ sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
