Hello! Longtime Lurker here.
I've been playing around with the idea that maybe SQLObject could use bound
parameters ("prepared statements") for some types of queries.
It seems that we generate a whole lot of queries that are really
get-by-id's, especially when we're iterating through a join. Although it's
been mentioned before that caching the result of a join would be a cool
thing, I just decided to tackle the simple case of reducing the load on the
DB engine of parsing all those getByID calls by making them calls to execute
a prepared statement.
Important point #1: PREPARED STATEMENTS EXECTUTE SIGNIFICANTLY FASTER than
parsed ones, especially when our standard query by id passes all the column
names in to make sure we know the order in which they're coming back, which
is a lot of text to parse, analyze, plan, and execute.
Although some SQL's are different in this regard, let's pretend for a moment
that we don't have to worry about cross engine compatibility and think in
Postgres mode; if this doesn't work in other engines, surely we can disable
it and use the existing retrieval in that case. But, many other engines do
'prepare's, so this might actually be portable.
Ok, so In Postgres, the prepare statement works as follows (example from
http://www.postgresql.org/docs/8.1/interactive/sql-prepare.html):
PREPARE nameOfPreparedStatement (int, date) AS
SELECT * FROM users u, logs l
WHERE u.usrid=$1 AND u.usrid=l.usrid
AND l.date = $2;
EXECUTE nameOfPreparedStatement(1, current_date);
Please bear in mind I've not delved into SQLObject internals very much, so
forgive the newbie errors. Further PLEASE forgive the wordy debugging
statements I've left in the below code since they might help someone
thinking about this problem further and/or testing it out.
OVERKILL REMOVED: I had put in this nice idea of creating a 'signature' for
each prepared query in the form of concat'ing the tablename with a list of
the params as a string and md5'ing the sucker to create a name for it (have
to do something; Postgres has a 64 char limit to a var name). Then, I
yanked it because I figured that if the query dies due to bad column names,
the logic below will automatically re-prepare the statement and try it
again. Regardless, it's an alternative to the simpler
"sqlobject_getbyid_tablename" naming convention.
So, after some looking and puzzling over the obfuscated meaning of
selectResults and other minutiae, I thought the best place for this is in
sqlobject/main.py, about line 950, in SQLObject class's _init() method.
Here's the code. It works for some trivial cases but not others (joins?),
and I've not figured that part out yet. But, I would like to kindly SOLICIT
ADVICE if this is a good idea, if this is doomed, if this has been tried
before, and other general opinions from the crowd before any attempt to
proceed further, and hope that any advice would come with specific
suggestionsÂ…
Pseudocode:
- come up with the prep'd statement's name,
- try to execute it;
- if that doesn't work:
- destroy anything by that name,
- try to recreate it,
- try to run it again, and
- if that doesn't work:
- failover to the old way.
Current bug (can't get past) is that _SO_foreignKey is calling get(), get()
is dying complaining that the id field contains a tuple of row data instead
of an int.
ALSO: It occurs to me I might be able to generalize this stuff by putting
the below code into sqlobject/dbconnection.py's DBAPI._SO_selectOneAlt(),
and just prepare all statements there. DonÂ’t know, suggestions welcome!
Enjoy!
#------------------------------------------- code start
---------------------
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
if not selectResults:
#print "*" * 1000
import sys, traceback
from pprint import pprint, pformat
selectResults = None
prepName = ''
if type(self.id) == type(5):
try:
try:
columnList = self.sqlmeta.columnList
cols = ','.join([x.dbName for x in
self.sqlmeta.columnList])
print "cols = ", cols
prepName = 'sqlobject_getByID_%s' %
str(self.__class__.__name__)
executeString = "execute %s(%s);" % (prepName, self.id)
print "Initial ExecuteString: %s"% (executeString)
selectResults = self._connection.queryAll(executeString)
except:
msg = apply(traceback.format_exception, sys.exc_info())
print "Execute-Level Traceback: %s" % (msg)
try:
deallocString = "deallocate %s;" % (prepName)
#print "Deallocate string: %s" % (deallocString)
selectResults =
self._connection.query(deallocString)
except:
msg = apply(traceback.format_exception,
sys.exc_info())
print "Deallocate-Level Traceback: (string=%s): %s"
% (deallocString, msg)
pass
try:
prepString = 'Prepare %s (int) as select %s from %s
where %s.%s = $1;' % (
prepName, cols, self.__class__.__name__,
self.__class__.__name__, self.sqlmeta.idName)
print "Prepstring = %s" % (prepString)
selectResults = self._connection.query(prepString)
except:
msg = apply(traceback.format_exception,
sys.exc_info())
print "Prepare-Level Traceback: %s" % (msg)
pass
try:
executeString = "execute %s(%s);" % (prepName,
self.id)
print "Failover ExecuteString: %s"% (executeString)
selectResults =
self._connection.queryAll(executeString)
print "select results:", pformat(selectResults)
except:
msg = apply(traceback.format_exception,
sys.exc_info())
print "Failover Execute-Level Traceback: %s" % (msg)
pass
except:
msg = apply(traceback.format_exception, sys.exc_info())
print "Unknown-Level Traceback: %s" % (msg)
pass
dbNames = [col.dbName for col in self.sqlmeta.columnList]
if not selectResults:
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
-- 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)
___________________________________
-------------------------------------------------------------------------
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
