[Zope-DB] Parametric queries

[Charlie Clark]

On 2005-07-30 at 19:53:12 [+0200], Jim Abramson <jabramson at wgen.net> wrote:
> > It does not matter. My point is: most of my database-driven
> > applications are "fast" enough (fast enough means that nobody
> > complains about speed). In the case where I had slow response
> > times than this was caused either by non-DB code or by
> > unoptimized SQL queries, missing indexes etc...nothing where
> > prepared SQL statement would have solved my life.
> > 
> 
> It *does* very much matter, for example, when your application is
> connected to a heavily-loaded production database, and the overhead of
> your db recompiling that same query, over and over again, tips the
> balance on the db server's CPU btn the black and the red.

I think the answer is here: it depends! If you are passing a large number 
of results into a ZPT then time spent in the database is likely to be a lot 
less than rendering the PageTemplate. If, however, you are using Zope to 
make a database driven application then you are very much likely to notice 
the difference.

> Of course caching query results in-memory can help save a trip
> altogether, but that is not always the proper solution (does that cached
> result really still match what's in the db?  and it's not much help with
> inserts/updates either.)
 
> The lack of binding variable support in ZSQL(Zope2.x) is one of the
> handful of things that ultimately drove me out of "instance space" and
> onto the filesystem.  (The other biggies: Zope hijacking RDBMS
> transactions - IMHO, that's none of Zope's business - and lack of
> support for any sane version control process.)  It was not an easy
> transition to make.  So I'd strongly suggest to anyone beginning a Zope
> project, who is even mulling over such RDBMS-related concerns as these,
> go straight to FS-based development, in order that you can take
> advantage of Zope's vast feature set without becoming imprisoned by such
> limitations.

The internals of RDBMS integration of Zope work but are certainly nothing 
to write home about: Zope has to parse the query string to check it 
contains valid SQL and sets up it's own cache management. Unfortunately 
because the situation is "okay" for most developers, it's been left as it 
is for far too long: better integration of RDBMS in Zope would mean both 
less and simpler code making it easier to make DAs for the various Python 
DB drivers resulting in quicker and better development of faster 
applications. There is a definite bias in Zope development towards the ZODB 
- this is Jim Fulton's openly preferred situation - or "cool" 
object-relational stuff like SQLObject or Plone Archetypes. This is a pity 
because ZSQL-Methods and DA's provide an excellent start to integrate RDMS 
in Zope: it feels like the right way to do it. But under the hood there are 
many things which could be improved upon.

Charlie