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

[raymond.hettinger]

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
 '''