Re: [SQLObject] SQLObject Performance

[Ian B. <ia...@co...>]

Colin Stewart wrote:
> Is it safe to use the DBAPI.getConnection() call to get the underlying 
> connection and use this mixed in with ordinary SQLObject calls?  It 
> would be nice to be able to mix raw SQL and SQLObject as needed over the 
> same connection.  I'm not sure how SQLObject's caching works, but 
> there's presumably a mechanism for invalidating results from individual 
> tables?

If you look back in the archives a couple weeks, I outlined some ways to 
expire the cache manually.  Generally if you are doing inserts it should 
be fine, as there's nothing related to inserts that gets cached.  Joins 
and selects are not cached (except for .get), though they will returns 
the cached versions of objects.  With an insert, this isn't an issue. 
Doing deletes or updates could cause cache consistency problems, so 
you'd have to expire the cache.

>>/> Is it unrealistic to use SQLObject for DB interaction when handling 
>>> batch loads of data?  I've done a quick profile of the code (top few 
>>> calls below) and nothing jumps out as being particularly easy to optimise...
>>> 
>>>    ncalls  tottime  percall  cumtime  percall filename:lineno(function)
>>> 15308/14042    2.500    0.000    4.430    0.000 converters.py:179(sqlrepr)
>>
>>It's interesting that sqlrepr is at the top.  I'll have to think about 
>>how I'm using it.  It's also been suggested that SQLObject rely on the 
>>database driver's quoting instead of doing its own.  This may lend more 
>>weight to that opinion./
>>
> 
> 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.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org