[Python-checkins] r69232 - in sandbox/trunk/dbm_sqlite/alt: dbsqlite.py time_sqlite.py

Tue Feb 3 02:24:47 CET 2009

Author: raymond.hettinger
Date: Tue Feb 3 02:24:46 2009
New Revision: 69232
Log:
Faster version of __len__().
Modified:
 sandbox/trunk/dbm_sqlite/alt/dbsqlite.py
 sandbox/trunk/dbm_sqlite/alt/time_sqlite.py
Modified: sandbox/trunk/dbm_sqlite/alt/dbsqlite.py
==============================================================================

--- sandbox/trunk/dbm_sqlite/alt/dbsqlite.py	(original)
+++ sandbox/trunk/dbm_sqlite/alt/dbsqlite.py	Tue Feb 3 02:24:46 2009
@@ -37,7 +37,7 @@
 self.conn.commit()
 
 def __len__(self):
- GET_LEN = 'SELECT COUNT(*) FROM shelf'
+ GET_LEN = 'SELECT MAX(ROWID) FROM shelf'
 return self.conn.execute(GET_LEN).fetchone()[0]
 
 def keys(self):
@@ -63,7 +63,7 @@
 raise KeyError(key)
 return item[0]
 
- def __setitem__(self, key, value):
+ def __setitem__(self, key, value): 
 ADD_ITEM = 'REPLACE INTO shelf (key, value) VALUES (?,?)'
 self.conn.execute(ADD_ITEM, (key, value))
 #self.conn.commit()
@@ -71,7 +71,7 @@
 def __delitem__(self, key):
 if key not in self:
 raise KeyError(key)
- DEL_ITEM = 'DELETE FROM shelf WHERE key = ?'
+ DEL_ITEM = 'DELETE FROM shelf WHERE key = ?' 
 self.conn.execute(DEL_ITEM, (key,))
 #self.conn.commit()
 
@@ -84,8 +84,8 @@
 if kwds:
 self.update(kwds)
 
- def clear(self):
- CLEAR_ALL = 'DELETE FROM shelf; VACUUM;'
+ def clear(self): 
+ CLEAR_ALL = 'DELETE FROM shelf; VACUUM;' 
 self.conn.executescript(CLEAR_ALL)
 self.conn.commit()
 
@@ -96,27 +96,27 @@
 self.conn = None
 
 def __del__(self):
- self.close()
+ self.close() 
 
 class ListRepr:
 
 def __repr__(self):
- return repr(list(self))
+ return repr(list(self)) 
 
 class SQLhashKeysView(collections.KeysView, ListRepr):
-
+ 
 def __iter__(self):
 GET_KEYS = 'SELECT key FROM shelf ORDER BY ROWID'
 return map(itemgetter(0), self._mapping.conn.cursor().execute(GET_KEYS))
 
 class SQLhashValuesView(collections.ValuesView, ListRepr):
-
+ 
 def __iter__(self):
 GET_VALUES = 'SELECT value FROM shelf ORDER BY ROWID'
 return map(itemgetter(0), self._mapping.conn.cursor().execute(GET_VALUES))
 
 class SQLhashItemsView(collections.ValuesView, ListRepr):
-
+ 
 def __iter__(self):
 GET_ITEMS = 'SELECT key, value FROM shelf ORDER BY ROWID'
 return iter(self._mapping.conn.cursor().execute(GET_ITEMS))
@@ -131,7 +131,7 @@
 for d in SQLhash(), SQLhash('example'):
 print(list(d), "start")
 d['abc'] = 'lmno'
- print(d['abc'])
+ print(d['abc']) 
 d['abc'] = 'rsvp'
 d['xyz'] = 'pdq'
 print(d.items())
@@ -140,7 +140,7 @@
 print(list(d), 'list')
 d.update(p='x', q='y', r='z')
 print(d.items())
-
+ 
 del d['abc']
 try:
 print(d['abc'])
@@ -148,7 +148,7 @@
 pass
 else:
 raise Exception('oh noooo!')
-
+ 
 try:
 del d['abc']
 except KeyError:
Modified: sandbox/trunk/dbm_sqlite/alt/time_sqlite.py
==============================================================================
--- sandbox/trunk/dbm_sqlite/alt/time_sqlite.py	(original)
+++ sandbox/trunk/dbm_sqlite/alt/time_sqlite.py	Tue Feb 3 02:24:46 2009
@@ -63,6 +63,8 @@
 'SELECT key FROM shelf',
 'SELECT value FROM shelf',
 'SELECT key, value FROM shelf',
+ 'SELECT COUNT(*) FROM shelf',
+ 'SELECT MAX(ROWID) FROM shelf',
 ]
 
 FINDKEY = [
@@ -123,12 +125,13 @@
 return '%.2f' % (clock() - start)
 
 n, m = 2000, 6000
-fragment, vacuum = True, True
+
 seed('xyzpdqbingo')
 items = populate(n)
-if 0:
- dellist = [(randrange(n),) for i in range(m)]
- addlist = populate(m)
+if 1:
+ fragment, vacuum = False, False
+## dellist = [(randrange(n),) for i in range(m)]
+## addlist = populate(m)
 for stmt in SELECTORS:
 print(stmt)
 for builder, name in [MAKE_SHELF, MAKE_SHELF_PRIMARY, MAKE_SHELF_UNIQUE]:
@@ -137,7 +140,7 @@
 fragmentit(conn, addlist, dellist)
 if vacuum:
 conn.execute('VACUUM')
- print(sorted(timeit(conn, stmt, (), n=100) for i in range(6)), name)
+ print(sorted(timeit(conn, stmt, (), n=10000) for i in range(6)), name)
 print()
 else:
 pairs = sample(items, 3)
@@ -165,6 +168,10 @@
 Missing key search 8% faster than a found key
 "SELECT 1" is 3% faster than "SELECT key" which is 3% faster than "SELECT value"
 
+Finding the length of the table:
+ "SELECT MAX(ROWID)" beats "SELECT COUNT(*)"
+ Disassembly shows the former goes straight to the last record,
+ while the latter does a full table scan.
 
 ----- Unfragmented: n, m = 2000, 6000
 
@@ -291,4 +298,12 @@
 ['0.57', '0.57', '0.57', '0.58', '0.58', '0.58'] b'jgmOI'
 ['0.55', '0.55', '0.55', '0.56', '0.56', '0.57'] b'oNJuV'
 
+
+----- Find length of the table -----
+
+SELECT COUNT(*) FROM shelf
+['2.36', '2.37', '2.37', '2.37', '2.39', '2.47'] PRIMARY
+
+SELECT MAX(ROWID) FROM shelf
+['0.50', '0.50', '0.50', '0.50', '0.51', '0.55'] PRIMARY
 '''
