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;
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…

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


#------------------------------------------- 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):
                    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)
                    msg = apply(traceback.format_exception, sys.exc_info())
                    print "Execute-Level Traceback: %s" % (msg)
                        deallocString = "deallocate %s;" % (prepName)
                        #print "Deallocate string: %s" % (deallocString)
                        selectResults = self._connection.query(deallocString)
                        msg = apply(traceback.format_exception, sys.exc_info())
                        print "Deallocate-Level Traceback: (string=%s): %s" % (deallocString, msg)
                        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)
                        msg = apply(traceback.format_exception, sys.exc_info())
                        print "Prepare-Level Traceback: %s" % (msg)
                        executeString = "execute %s(%s);" % (prepName, self.id)
                        print "Failover ExecuteString: %s"% (executeString)
                        selectResults = self._connection.queryAll(executeString)
                        print "select results:", pformat(selectResults)
                        msg = apply(traceback.format_exception, sys.exc_info())
                        print "Failover Execute-Level Traceback: %s" % (msg)
                msg = apply(traceback.format_exception, sys.exc_info())
                print "Unknown-Level Traceback: %s" % (msg)

        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_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)