>I edited this second script to use plain apsw. >In the vi editor: >%s/db.execute/db.cursor().execute/ >%s/executescript/execute/ >%s/db.commit()/db.cursor().execute("COMMIT")/ >/Method 9 >.,$d > >(the carray tests are left out) >My test output for 1000 keys is: >$ python3 keith2b.py 1000 >Creating db and sample keys: 1000000 rows; 1000 keys >Method 1: Individual Row 00:00:00.003748 >Method 2: Individual Row (Sorted) 00:00:00.003545 >Method 3: Rows with ExecMany 00:00:00.003300 >Method 3: Rows with ExecMany Sort 00:00:00.003088 >Method 4: Using IN temp 00:00:00.003838 >Method 5: Using IN temp (sorted) 00:00:00.003850 >Method 6: Using IN temp no rowid 00:00:00.003941 >Method 7: Using IN (dynamic) 00:00:00.003276 >Method 8: Using IN (sorted) 00:00:00.003223 > >This is much different as in your output. >- the test is two times faster here (on a moderate system) >- there is no substantial difference any longer between individual tests >(excluded carray) > >Any idea? > >Thanks for the inviting tests.
I can actually. My apsw.Connection class is actually a python class that inherits from the real apsw.Connection class so that I can add a bunch of extra's to it. For example, the apsw.Connection.execute method(s) are not direct delegates to apsw.Connection.Cursor().execute method -- it also does some scanning of the bind parameters so that it can handle datetime objects conversion to text. Similarly I have an active exec tracer and row tracer so that output tuples are converted to objects and datetime data can be converted to real datetime objects. For the most part these overheads are static per execute call and per row returned so mostly cancel themselves out (but make everything somewhat slower). It is also why the execution of many select's degrades so quickly -- the setup time is per select -- and the executemany degrades slower (it is only calling back on the exe tracer for each statement rather than doing the whole overhead for each statement). The row tracer overhead is constant since the actual number of rows returned is constant on each set. Some other discrepancies are apparently native to the differences between running on Linux vs Windows 10 -- Windows 10 seems to be somewhat less deterministic in its scheduling and even CPython itself seems to be somewhat different between. So, I modified my delegate slightly so that if the exec/row tracer is unhooked then the bind parameters are not scanned either (but there is still a bit of overhead per statement executed due to the delegation). Here are the results: Full exec and row hook processing with bind parameter scanning: 1000000 rows; 1000 keys; 0.100000% Method 1: Individual Row 00:00:00.018191 54971 rps external order Method 2: Individual Row (Sorted) 00:00:00.018101 55244 rps external order (sorted) Method 3: Rows ExecuteMany 00:00:00.016854 59332 rps external order Method 3: Rows ExecuteMany Sorted 00:00:00.016159 61885 rps external order (sorted) Method 4: Using IN temp 00:00:00.007869 127088 rps order by id Method 5: Using IN temp (sorted) 00:00:00.008125 123083 rps order by id Method 6: Using IN keyset 00:00:00.009833 101697 rps order by id Method 7: Using IN keyset sorted 00:00:00.008166 122461 rps order by id Method 8: Using IN (dynamic) 00:00:00.004820 207474 rps order by id Method 9: Using IN (sorted) 00:00:00.005196 192452 rps order by id Method A: Using IN CArray 00:00:00.005440 183815 rps order by id Method B: Using IN CArray sorted 00:00:00.005891 169741 rps order by id No row or exec tracers, bind parameter scanning bypassed: 1000000 rows; 1000 keys; 0.100000% Method 1: Individual Row 00:00:00.003435 291089 rps external order Method 2: Individual Row (Sorted) 00:00:00.003366 297047 rps external order (sorted) Method 3: Rows ExecuteMany 00:00:00.002942 339950 rps external order Method 3: Rows ExecuteMany Sorted 00:00:00.002892 345807 rps external order (sorted) Method 4: Using IN temp 00:00:00.003435 291129 rps order by id Method 5: Using IN temp (sorted) 00:00:00.003419 292449 rps order by id Method 6: Using IN keyset 00:00:00.003649 274083 rps order by id Method 7: Using IN keyset sorted 00:00:00.003626 275814 rps order by id Method 8: Using IN (dynamic) 00:00:00.002526 395950 rps order by id Method 9: Using IN (sorted) 00:00:00.002706 369574 rps order by id Method A: Using IN CArray 00:00:00.002902 344557 rps order by id Method B: Using IN CArray sorted 00:00:00.002656 376508 rps order by id No row or exec tracers, calling .cursor() methods directly (so minimize delegation processing): 1000000 rows; 1000 keys; 0.100000% Method 1: Individual Row 00:00:00.003267 306108 rps external order Method 2: Individual Row (Sorted) 00:00:00.003083 324310 rps external order (sorted) Method 3: Rows ExecuteMany 00:00:00.002665 375262 rps external order Method 3: Rows ExecuteMany Sorted 00:00:00.002772 360738 rps external order (sorted) Method 4: Using IN temp 00:00:00.003426 291858 rps order by id Method 5: Using IN temp (sorted) 00:00:00.003491 286457 rps order by id Method 6: Using IN keyset 00:00:00.003555 281326 rps order by id Method 7: Using IN keyset sorted 00:00:00.003415 292857 rps order by id Method 8: Using IN (dynamic) 00:00:00.002914 343204 rps order by id Method 9: Using IN (sorted) 00:00:00.002677 373557 rps order by id Method A: Using IN CArray 00:00:00.002718 367856 rps order by id Method B: Using IN CArray sorted 00:00:00.002936 340612 rps order by id Using --sqlite3 (does not do executemany for select statements): 1000000 rows; 1000 keys; 0.100000% Method 1: Individual Row 00:00:00.004060 246303 rps external order Method 2: Individual Row (Sorted) 00:00:00.003985 250945 rps external order (sorted) Method 3: Rows ExecuteMany Method 3: Rows ExecuteMany Sorted Method 4: Using IN temp 00:00:00.003985 250930 rps order by id Method 5: Using IN temp (sorted) 00:00:00.003968 252031 rps order by id Method 6: Using IN keyset 00:00:00.004013 249201 rps order by id Method 7: Using IN keyset sorted 00:00:00.004247 235463 rps order by id Method 8: Using IN (dynamic) 00:00:00.002576 388217 rps order by id Method 9: Using IN (sorted) 00:00:00.002456 407174 rps order by id Note that I added rps (rows per second) which is merely int(rows / et) and added "order by id" to each query that returns multiple rows so that they will all get the same results in the same order for each test (except the first 4 which will of course retrieve the rows in the order asked for since you get one row per select). So yes, I would conclude that they are all about the same speed so it is really a question of which is the least work to implement, though overall I would probably favour the dynamic in clause format. Note that the parameter is now the exponent of the set, so 1 is 10 records, 2 is 100 records, ... 6 is 1 million records ... and the test runs for each power of ten up to that specified ... #! python3 import datetime import random import sys import time exponent = int(sys.argv[1]) elapsed = lambda et: datetime.datetime.utcfromtimestamp(et).time() tuplize = lambda x: (x,) carrray = None if '--sqlite3' in sys.argv: import sqlite3 db = sqlite3.connect(':memory:', isolation_level=None) else: import apsw if '--unhook' in sys.argv and hasattr(apsw, 'rowunhook'): apsw.rowunhook() db = apsw.Connection(':memory:') if hasattr(apsw, 'carray'): carray = apsw.carray datasize = 10 ** exponent print('Creating db and sample keys:', end=' ', flush=True) db.executescript(''' create table x ( id integer primary key, data blob ); insert into x with a(x) as ( select 1 union all select x + 1 from a where x < %d ) select x, randomblob(30) from a; analyze; ''' % (datasize,)) print(db.execute('select count(*) from x').fetchone()[0], 'rows') masterkeys = [i for i in range(datasize)] random.shuffle(masterkeys) for rowexp in range(exponent + 1): rows = 10 ** rowexp rowset = masterkeys[:rows] print() print(datasize, 'rows;', rows, 'keys;', '%f%%' % (rows*100/datasize,)) # ------------------------------------------------------------------------- try: print('Method 1: Individual Row ', end=' ', flush=True) st = time.time() db.executescript('BEGIN') for key in rowset: row = db.execute('select * from x where id=?', (key,)).fetchone() db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps external order') except: print() # ------------------------------------------------------------------------- try: print('Method 2: Individual Row (Sorted)', end=' ', flush=True) st = time.time() db.executescript('BEGIN') for key in sorted(rowset): row = db.execute('select * from x where id=?', (key,)).fetchone() db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps external order (sorted)') except: print() # ------------------------------------------------------------------------- try: print('Method 3: Rows ExecuteMany ', end=' ', flush=True) st = time.time() db.executescript('BEGIN') for row in db.executemany('select * from x where id=?', list(map(tuplize, rowset))): pass db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps external order') except: print() # ------------------------------------------------------------------------- try: print('Method 3: Rows ExecuteMany Sorted', end=' ', flush=True) st = time.time() db.executescript('BEGIN') for row in db.executemany('select * from x where id=?', list(map(tuplize, sorted(rowset)))): pass db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps external order (sorted)') except: print() # ------------------------------------------------------------------------- try: print('Method 4: Using IN temp ', end=' ', flush=True) st = time.time() db.executescript('BEGIN') db.executescript('create temporary table keys (key)') db.executemany('insert into keys values (?)', list(map(tuplize, rowset))) for row in db.execute('select * from x where id in temp.keys order by id'): pass db.executescript('drop table temp.keys') db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() # ------------------------------------------------------------------------- try: print('Method 5: Using IN temp (sorted) ', end=' ', flush=True) st = time.time() db.executescript('BEGIN') db.executescript('create temporary table keys (key)') db.executemany('insert into keys values (?)', list(map(tuplize, sorted(rowset)))) for row in db.execute('select * from x where id in temp.keys order by id'): pass db.executescript('drop table temp.keys') db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() # ------------------------------------------------------------------------- try: print('Method 6: Using IN keyset ', end=' ', flush=True) st = time.time() db.executescript('BEGIN') db.executescript('create temporary table keys (key primary key) without rowid') db.executemany('insert or ignore into keys values (?)', list(map(tuplize, rowset))) for row in db.execute('select * from x where id in temp.keys order by id'): pass db.executescript('drop table temp.keys') db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() # ------------------------------------------------------------------------- try: print('Method 7: Using IN keyset sorted ', end=' ', flush=True) st = time.time() db.executescript('BEGIN') db.executescript('create temporary table keys (key primary key) without rowid') db.executemany('insert or ignore into keys values (?)', list(map(tuplize, rowset))) for row in db.execute('select * from x where id in temp.keys order by id'): pass db.executescript('drop table temp.keys') db.commit() et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() # ------------------------------------------------------------------------- try: print('Method 8: Using IN (dynamic) ', end=' ', flush=True) st = time.time() for row in db.execute('select * from x where id in (' + ','.join(map(str, rowset)) + ') order by id'): pass et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() # ------------------------------------------------------------------------- try: print('Method 9: Using IN (sorted) ', end=' ', flush=True) st = time.time() for row in db.execute('select * from x where id in (' + ','.join(map(str, sorted(rowset))) + ') order by id'): pass et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() if carray: # ------------------------------------------------------------------------- try: print('Method A: Using IN CArray ', end=' ', flush=True) st = time.time() for row in db.execute('select * from x where id in carray(:l_address, :l_length, :l_type) order by id', carray('l', rowset)): pass et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() # ------------------------------------------------------------------------- try: print('Method B: Using IN CArray sorted ', end=' ', flush=True) st = time.time() for row in db.execute('select * from x where id in carray(:l_address, :l_length, :l_type) order by id', carray('l', sorted(rowset))): pass et = time.time() - st print(elapsed(et), '%7d' % (rows / et,), 'rps order by id') except: print() # ------------------------------------------------------------------------- -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users