Re: [SQLObject] SQLObject Performance

[Brad B. <br...@bb...>]

On Friday, October 8, 2004, at 06:40  PM, Ian Bicking wrote:

> Colin Stewart wrote:

[snip]

>> Using the db driver's quoting would also mean that the SQL can be 
>> cached as prepared statements by the DB.  For some (e.g. Oracle) that 
>> can lead to a pretty significant speed up in itself.
>
> I don't know enough about prepared statements to know if it would 
> help.  If it's possible to prepare, up front, three or four statements 
> for every class, then it could help.  If you have to prepare a 
> statement, then use it several times, then prepare another statement, 
> it's unlike to help, since SQLObject doesn't know enough to predict 
> what statements are going to be used in sequence often enough to help.
>
> Also, the postgres and mysql drivers (and probably sqlite) don't have 
> any prepared statements, and do all the quoting on the client side.  
> I'm most interested in those backends, so there's not a huge amount to 
> be gained.  They probably do the quoting in C, though, with some 
> performance gain there.

My interest is in PostgreSQL, so I'll comment on that. psycopg claims 
to support the DB-API v2, and thus should have prepared statements.

So here's a unit test that demonstrates, perhaps, a decent way of 
hooking up bare SQL to a method (untested, I stayed up till 4am last 
night playing Mao, and I'm off to Heathrow in 10 minutes, so please 
forgive me if this code is a bit dirty. :) This may need to be molded 
around slightly to solve your problem but I hope it illustrates an 
interesting new idea and gets some thinking going on about this problem:

class SQLMethodTest(SQLObjectTest):
     def testSQLMethod(self):
         class USAddressesResult(object):
             class USCitizen(object):
                 def __init__(self, id, name, address):
                     self.id = id
                     self.name = name
                     self.address = address

             def __init__(self, results):
                 self.results = results

             def __iter__(self):
                 for r in self.results:
                     yield USCitizen(r[0], r[1], r[2])

         class Person(SQLObject):
             usaddresses = SQLMethod(
                 sql = """\
                 SELECT p.id, p.name, a.address
                 FROM person p, address a
                 WHERE p.id = a.personid
                   AND a.country_code = 'US'""",
                 result = USAddressesResult)
             usaddresses = classmethod(usaddresses)

         uscitizens = list(Person.usaddresses())
         self.assertEquals(len(uscitizens), 2)
         self.assertEquals(uscitizens[0].name, "Ford Prefect")
         self.assertEquals(uscitizens[0].address, "Somewhere near 
Betelgeuse")

The benefits gained from this feature would be:

1. The speed of working with raw SQL.

2. Being able to get a resultset that actually does what you need 
(since the current concept that "an element in a result set maps to one 
row in a table" often doesn't make sense, as each row returned from a 
SELECT query is often *not* a row in a table.)

3. You could, of course, use placeholders in the SQL statement and then 
the SQLMethod method would accept that many params.

Thoughts, comments, feedback welcome.

Gotta run,

--
Brad Bollenbach