Hello / Privyet / Zdrastvoychye / Bongiorno / Aloha / Howdy -
!!! Reasonable Success !!!
Well, I had a free evening here so I just stayed at work.
Here's my first cut at this feature:
* I've tested it at between 20% and 40% faster (exact results below).
* It is completely optional / requires no code changes to existing code.
* Code NOT using this feature will run at its current speed.
* To use it: add a sqlmeta line, plus add lines to both your create
connection / transaction call, and add a param to your createTransaction
calls.
* The only sqlobject code modified is main.py's class SQLObject _init().
* Below is the diff for main.py and the getTransaction example.
* Attached is a file with testing code.
This is the new init:
def _init(self, id, connection=None, selectResults=None):
assert id is not None
# This function gets called only when the object is
# created, unlike __init__ which would be called
# anytime the object was returned from cache.
self.id = id
self._SO_writeLock = threading.Lock()
# If no connection was given, we'll inherit the class
# instance variable which should have a _connection
# attribute.
if connection is not None:
self._connection = connection
# Sometimes we need to know if this instance is
# global or tied to a particular connection.
# This flag tells us that:
self.sqlmeta._perConnection = True
gotAnswer = False
prepName = None
try:
prepName = self.sqlmeta.getByIDPrepName
except AttributeError:
pass
# could add assert here if wanted.
if not selectResults and self.sqlmeta._perConnection and prepName:
statement = "execute %s (%s)" % (prepName, self.id)
import sys, traceback
try:
selectResults = self._connection.queryOne(statement)
gotAnswer = True
except:
msg = apply(traceback.format_exception, sys.exc_info())
print "Could not fetch using prepared statement: %s" % (msg)
if not gotAnswer and not selectResults:
dbNames = [col.dbName for col in self.sqlmeta.columnList]
selectResults = self._connection._SO_selectOne(self, dbNames)
if not selectResults:
raise SQLObjectNotFound, "The object %s by the ID %s does not
exist" % (self.__class__.__name__, self.id)
self._SO_selectInit(selectResults)
self._SO_createValues = {}
self.dirty = False
The class definition changes to add one sqlmeta:
class Org(SQLObject):
class sqlmeta:
idName = 'OrgID'
table = 'Org'
getByIDPrepName = table + '_getByID'
The getTransaction everyone uses in their own code changes to something like
this:
def getTransaction(tableList = []):
dsn = "host=127.0.0.1 dbname=dbrel203 user=krice password=chicken4lunch"
con = PostgresConnection(dsn, debug=0)
for t in tableList:
prepName = None
try:
prepName = t.sqlmeta.getByIDPrepName
except AttributeError:
print "No getByIDPrepName defined."
assert prepName, "Must define a sqlmeta getByIDPrepName for each
table object passed into getTransaction."
dbNames = [col.dbName for col in t.sqlmeta.columnList]
prep = "prepare %s (int) as select %s from %s where (%s.%s) = $1;" %
\
(t.sqlmeta.table + '_getByID',
', '.join([x for x in dbNames]),
t.sqlmeta.table,
t.sqlmeta.table,
t.sqlmeta.idName)
print "Table: %s, prep=%s." % (t.__name__, prep)
result = con.query(prep)
print "Result: %s" % result
return con
Then, when declaring transactions, pass in the names of the tables for which
the getByIDPrepName is defined.
t= getTransaction(tableList=[TableName1, TableName2, ...])
There's one (sort of optional) thing to do is make it more fault-tolerant.
That is, the getTransaction call has to match what's defined in the
SQLObject class, or there's a traceback. I'll probably do this when I plug
this into a branch of our codebase.
Here's what I found from timing tests on the attached test program:
[ ALL OLD/NEW MEASUREMENTS IN SECONDS)
Small OrgUser object (2 fields):
With 4 users per org, 400 iterations of the loop: old=53.91, new=44.09
(17% faster)
With 80 users per org, 30 iterations of the loop: old=139.17, new=101.47
(37% faster)
Large OrgUser object (50 fields):
With 80 users per org, 30 iterations of the loop: old=172.95, new=123.53
(40% faster)
With 150 users per org, 30 iterations of the loop: old=352.41, new=234.01
(50% faster)
I'm not sure how people count these numbers, they could also be:
44/53 = .83 = 17% faster
101/139 = .72 = 28% faster
123/172 = .715 = 29% faster
234/352 = .665 = 34% faster
Regardless, for a simple hack, this seems like it could pay some
dividends...
Enjoy!
-- Kevin
___________________________________
Kevin J. Rice
Senior Software Engineer, Textura LLC
51-K Sherwood Terrace, Lake Bluff IL
(847) 235-8437 (spells VISAFLUIDS)
(847) 845-7423 (845-RICE, cellphone)
___________________________________
# test multiple selects
# Kevin J. Rice (http://justanyone.com)
runWith = True
lotsOfColumns = True
import site, sys, os
site.addsitedir(os.path.realpath(__file__))
site.addsitedir(os.path.realpath('Lib/external'))
from sqlobject import *
from pprint import pprint, pformat
from sqlobject.sqlbuilder import IN
from sqlobject.col import popKey, pushKey
import sys, traceback
class SOTextCol(SOStringCol):
def __init__(self, **kw):
popKey(kw, 'length')
popKey(kw, 'varchar')
SOStringCol.__init__(self, **kw)
class TextCol(Col):
baseClass = SOTextCol
def getTransaction(tableList = []):
dsn = "host=127.0.0.1 dbname=dbrel203 user=krice password=chicken4lunch"
con = PostgresConnection(dsn, debug=0)
for t in tableList:
prepName = None
try:
prepName = t.sqlmeta.getByIDPrepName
except AttributeError:
print "*" * 80
print "No getByIDPrepName defined."
assert prepName, "Must define a sqlmeta getByIDPrepName for each table
object passed into getTransaction."
dbNames = [col.dbName for col in t.sqlmeta.columnList]
prep = "prepare %s (int) as select %s from %s where (%s.%s) = $1;" % \
(t.sqlmeta.table + '_getByID',
', '.join([x for x in dbNames]),
t.sqlmeta.table,
t.sqlmeta.table,
t.sqlmeta.idName)
print "Table: %s, prep=%s." % (t.__name__, prep)
result = con.query(prep)
print "Result: %s" % result
return con
class Org(SQLObject):
class sqlmeta:
idName = 'OrgID'
table = 'Org'
if runWith:
getByIDPrepName = table + '_getByID'
orgName = TextCol(dbName='orgName', default=None)
users = MultipleJoin('OrgUser', joinColumn='myOrgID')
class OrgUser(SQLObject):
class sqlmeta:
idName = 'OrgUserID'
table = 'OrgUser'
if runWith:
getByIDPrepName = table + '_getByID'
firstName = TextCol(dbName='firstName', default=None)
myOrgID = KeyCol(dbName='myOrgID', default=None, foreignKey="Org")
if lotsOfColumns:
fieldNum01 = StringCol( dbName='fieldNum01', default='')
fieldNum02 = StringCol( dbName='fieldNum02', default='')
fieldNum03 = StringCol( dbName='fieldNum03', default='')
fieldNum04 = StringCol( dbName='fieldNum04', default='')
fieldNum05 = StringCol( dbName='fieldNum05', default='')
fieldNum06 = StringCol( dbName='fieldNum06', default='')
fieldNum07 = StringCol( dbName='fieldNum07', default='')
fieldNum08 = StringCol( dbName='fieldNum08', default='')
fieldNum09 = StringCol( dbName='fieldNum09', default='')
fieldNum10 = StringCol( dbName='fieldNum10', default='')
fieldNum11 = StringCol( dbName='fieldNum11', default='')
fieldNum12 = StringCol( dbName='fieldNum12', default='')
fieldNum13 = StringCol( dbName='fieldNum13', default='')
fieldNum14 = StringCol( dbName='fieldNum14', default='')
fieldNum15 = StringCol( dbName='fieldNum15', default='')
fieldNum16 = StringCol( dbName='fieldNum16', default='')
fieldNum17 = StringCol( dbName='fieldNum17', default='')
fieldNum18 = StringCol( dbName='fieldNum18', default='')
fieldNum19 = StringCol( dbName='fieldNum19', default='')
fieldNum20 = StringCol( dbName='fieldNum20', default='')
fieldNum21 = StringCol( dbName='fieldNum21', default='')
fieldNum22 = StringCol( dbName='fieldNum22', default='')
fieldNum23 = StringCol( dbName='fieldNum23', default='')
fieldNum24 = StringCol( dbName='fieldNum24', default='')
fieldNum25 = StringCol( dbName='fieldNum25', default='')
fieldNum26 = StringCol( dbName='fieldNum26', default='')
fieldNum27 = StringCol( dbName='fieldNum27', default='')
fieldNum28 = StringCol( dbName='fieldNum28', default='')
fieldNum29 = StringCol( dbName='fieldNum29', default='')
fieldNum30 = StringCol( dbName='fieldNum30', default='')
fieldNum31 = StringCol( dbName='fieldNum31', default='')
fieldNum32 = StringCol( dbName='fieldNum32', default='')
fieldNum33 = StringCol( dbName='fieldNum33', default='')
fieldNum34 = StringCol( dbName='fieldNum34', default='')
fieldNum35 = StringCol( dbName='fieldNum35', default='')
fieldNum36 = StringCol( dbName='fieldNum36', default='')
fieldNum37 = StringCol( dbName='fieldNum37', default='')
fieldNum38 = StringCol( dbName='fieldNum38', default='')
fieldNum39 = StringCol( dbName='fieldNum39', default='')
fieldNum40 = StringCol( dbName='fieldNum40', default='')
fieldNum41 = StringCol( dbName='fieldNum41', default='')
fieldNum42 = StringCol( dbName='fieldNum42', default='')
fieldNum43 = StringCol( dbName='fieldNum43', default='')
fieldNum44 = StringCol( dbName='fieldNum44', default='')
fieldNum45 = StringCol( dbName='fieldNum45', default='')
fieldNum46 = StringCol( dbName='fieldNum46', default='')
fieldNum47 = StringCol( dbName='fieldNum47', default='')
fieldNum48 = StringCol( dbName='fieldNum48', default='')
fieldNum49 = StringCol( dbName='fieldNum49', default='')
fieldNum50 = StringCol( dbName='fieldNum50', default='')
t= getTransaction()
try:
Org.dropTable(connection=t)
OrgUser.dropTable(connection=t)
except:
pass
t= getTransaction()
Org.createTable(connection=t)
OrgUser.createTable(connection=t)
userNameList1 = '''bill bob dave ed john steve luke mark john
matt joseph randy andy wendy carrie will michelle rebecca lisa mary'''.split()
orgNameList1 = '''Exxon Mobil Chevron Texaco Phillips66 BP Amoco
Aramco Lukoil Gazprom 3M UBS Warburg IBM JCPenny NYSE CPSE ABC'''.split()
userNameList = range(1, 100)
orgNameList = orgNameList1
if runWith:
t= getTransaction(tableList=[Org, OrgUser])
else:
t= getTransaction()
#x = dieNow()
i = 0
for oname in orgNameList:
newOrg = Org(orgName=oname, connection=t)
for x in range(1, 150):
OrgUser(firstName="Name_%s" % x, myOrgID=newOrg.id, connection=t)
if runWith:
t= getTransaction(tableList=[Org, OrgUser])
else:
t= getTransaction()
from mx.DateTime import now, Date
start = now()
l = 0
print "starting at: ", start
for i in range(1, 30):
#print "*" * 300
if i % 10 == 0:
print "i=%s" %i
for thisOrg in Org.select(IN(Org.q.orgName, orgNameList), connection=t):
string = "Have Org: %s, userslist=%s." % (thisOrg.orgName, \
pformat([x.firstName for x in thisOrg.users]))
l += len(string)
end = now()
print "ending at : %s elapsed = %s, l=%s" % (end, end - start, l)
# SQL statement for the same thing:
# select firstname, orgName from Org, OrgUser where Org.orgID = OrgUser.myOrgID;
-------------------------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc.
Still grepping through log files to find problems? Stop.
Now Search log events and configuration files using AJAX and a browser.
Download your FREE copy of Splunk now >> http://get.splunk.com/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
