[Zope-DB] Parametric queries

[Jim Abramson]

> --On 29. Juli 2005 22:22:38 +0200 Marcin Wudarczyk 
> <mar at mar.prv.pl> wrote:
> 
> > Andreas Jung wrote:
> >>> For the record: the overhead is about 40% on non-cached queries.r
> >>
> >> That's why were are using CCSQL methods :-)
> >
> > Not really - CCSQL caches results of the same query. 
> Caching queries 
> > in compiled form in DB server allows to speed up execution of all 
> > further queries (i.e. for any parameters) based on that query.
> >
> 
> 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.  

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.

Jim