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 ()
