Mailing List Archive

--On 28. Juli 2005 21:26:35 +0200 Marcin Wudarczyk <mar@mar.prv.pl> wrote:

>
> Hello,
>
> I've read the manual for Z SQL method and set up a simple example with
> query having a parameter. Everything works fine but then I realised that
> parametric Z SQL Methods are not passing its parameters to DB as
> parameters but inserting it into SQL string. I think this has
> significant impact on perfomance - DBs have special facilities to cache
> compiled queries and change only parameters. Is there a way to use this
> features from Zope?
>

Not really.

> (I understand that I can manually make queries via DB-API, but I loose
> sync with Zope transactions? Anyway I would prefer to use Z SQL Methods)

I've never had the case where ZSQL methods were *slow* whatever this
means. There are usually parts inside an application that are slower :-)
It's nice2have feature but you can live without it.

-aj

On 2005-07-28 at 21:52:28 [+0200], Andreas Jung <lists@andreas-jung.com>
wrote:
> Not really.
>
> > (I understand that I can manually make queries via DB-API, but I loose
> > sync with Zope transactions? Anyway I would prefer to use Z SQL Methods)
>
> I've never had the case where ZSQL methods were *slow* whatever this
> means. There are usually parts inside an application that are slower :-)
> It's nice2have feature but you can live without it.

For the record: the overhead is about 40% on non-cached queries.

Marcin, you will have to write your own ZSQL replacement to do this but
also need a Zope-DA which will work with parametric queries as nearly all
of them to do the quoting à la Zope, and essentially call the .query()
method. Our mxODBCZopeDA does allow *you* to pass parameters in.

Charlie
--
Charlie Clark
eGenix.com

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ... http://www.egenix.com/
>>> mxODBC.Zope.Database.Adapter ... http://zope.egenix.com/
>>> mxODBC, mxDateTime, mxTextTools ... http://python.egenix.com/
________________________________________________________________________

::: Try mxODBC.Zope.DA for Windows,Linux,Solaris,FreeBSD for free ! ::::
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

--On 29. Juli 2005 06:54:00 +0200 Charlie Clark <charlie@egenix.com> wrote:

>
> On 2005-07-28 at 21:52:28 [+0200], Andreas Jung <lists@andreas-jung.com>
> wrote:
>> Not really.
>>
>> > (I understand that I can manually make queries via DB-API, but I loose
>> > sync with Zope transactions? Anyway I would prefer to use Z SQL
>> > Methods)
>>
>> I've never had the case where ZSQL methods were *slow* whatever this
>> means. There are usually parts inside an application that are slower :-)
>> It's nice2have feature but you can live without it.
>
> For the record: the overhead is about 40% on non-cached queries.r

That's why were are using CCSQL methods :-)

-aj

Yeah, for my purposes, ZSQL Methods have been totally adequate, if not
perfect. I have not seen any performance issues, even if they have
that 40% overhead. Actually, the only thing that is slow is running
very large reports, but you cant really get around that when you call
up thousands of records in one shot. (or can you?) Of course, I only
have my apps running with 60-70 users, so I cant speak for those
running at high capacity. I would just use the already built zope
machinery instead of dealing with all that yourself, if you can help
it.

Also (and I am quite ignorant when it comes to the details on db
connections), if you are concerned about caching and the performance
of your queries, I am guessing you might be running several queries in
one operation. If so, wouldnt the use of stored procedures help with
that? That would reduce the connection overhead and I think the
parameters you spoke of could be used, right? Although, it is still
just building the sql command from zope. Just a thought I had. I use
procedures as much as possible and in my experience it really helps
performance.

Anyway, just thought I'd throw my 2 cents out there. I could be way
off on this.

Greg


On 7/28/05, Marcin Wudarczyk <mar@mar.prv.pl> wrote:
>
> Hello,
>
> I've read the manual for Z SQL method and set up a simple example with
> query having a parameter. Everything works fine but then I realised that
> parametric Z SQL Methods are not passing its parameters to DB as
> parameters but inserting it into SQL string. I think this has
> significant impact on perfomance - DBs have special facilities to cache
> compiled queries and change only parameters. Is there a way to use this
> features from Zope?
>
> (I understand that I can manually make queries via DB-API, but I loose
> sync with Zope transactions? Anyway I would prefer to use Z SQL Methods)
>
> Regards
>
> Marcin Wudarczyk
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>


--
Greg Fischer
1st Byte Solutions
http://www.1stbyte.com
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

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.

Cheers.


_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

--On 29. Juli 2005 22:22:38 +0200 Marcin Wudarczyk <mar@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.

-aj

> --On 29. Juli 2005 22:22:38 +0200 Marcin Wudarczyk
> <mar@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

_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

On 2005-07-30 at 19:53:12 [+0200], Jim Abramson <jabramson@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
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db