Mailing List Archive

Quoting JPenny@ykksnap-america.com <JPenny@ykksnap-america.com>:
> Yes, with a stored procedure the DB does not have to reparse and
> prepare a new plan for every query. This can be a major win. Esp.
> on Oracle.

Does ZSQL allow the use of bind variables? If so and the database has
a correctly sized query cache, there shouldn't be much reparsing for
repeated queries.
_______________________________________________
Zope-DB mailing list
Zope-DB@zope.org
http://mail.zope.org/mailman/listinfo/zope-db

No, ZSQL really predates bind variables. That is, they we
available on a few systems, but were rare. If the Oracle
specialist has a reason for going to external methods, like
his server is seriously loaded, I would pay attention to him.
If he is just following some set of "best practices", well, that
is a political problem for Remy.

Using external methods will be more work for the zope writer.
I don't know enough to comment seriously on security issues,
but I think that using procedures, like using bind variables, will
make SQL Injection much harder.





Cynthia Kiser <cnk+zope@caltech.edu>
02/17/2009 06:44 PM

To
JPenny@ykksnap-america.com
cc
Remy Pinsonnault <remypinsonnault@gmail.com>, zope-db@zope.org
Subject
Re: [Zope-DB] [Zope] Stored Procedures Versus ZSQL Methods






Quoting JPenny@ykksnap-america.com <JPenny@ykksnap-america.com>:
> Yes, with a stored procedure the DB does not have to reparse and
> prepare a new plan for every query. This can be a major win. Esp.
> on Oracle.

Does ZSQL allow the use of bind variables? If so and the database has
a correctly sized query cache, there shouldn't be much reparsing for
repeated queries.


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

Am 18.02.2009, 00:58 Uhr, schrieb <JPenny@ykksnap-america.com>:

> Using external methods will be more work for the zope writer.
> I don't know enough to comment seriously on security issues,
> but I think that using procedures, like using bind variables, will
> make SQL Injection much harder.

The mxODBC Zope DA makes the execute() method available to connection object instances which allows for parameter binding and the next release will make this available for PythonScripts. Rather than use ExternalMethods, however, I'd suggest that you use Views instead which make tying everything together a lot easier.

Regarding performance: the comparisons we did a few years ago suggested that parameter binding is around 40% faster for non-cached access from Zope as Zope does quite a lot of work to turn ZSQL methods into usable queries. If the caching works for you then you will have pretty good performance because Zope will only actually run the query for something that isn't in the cache. Stored procedures can offer a performance improvement if you plan to manipulate the data in any way, ie. if you want to get data out of several views and do something with it before you pass it to the browser. But most importantly - in the Zope world the RDBMS is unlikely ever to be your bottleneck.

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 our new mxODBC.Connect Python Database Interface for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/


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

Thanks for your help!
Rémy

On Wed, Feb 18, 2009 at 3:53 AM, Charlie Clark <charlie@egenix.com> wrote:

> Am 18.02.2009, 00:58 Uhr, schrieb <JPenny@ykksnap-america.com>:
>
> > Using external methods will be more work for the zope writer.
> > I don't know enough to comment seriously on security issues,
> > but I think that using procedures, like using bind variables, will
> > make SQL Injection much harder.
>
> The mxODBC Zope DA makes the execute() method available to connection
> object instances which allows for parameter binding and the next release
> will make this available for PythonScripts. Rather than use ExternalMethods,
> however, I'd suggest that you use Views instead which make tying everything
> together a lot easier.
>
> Regarding performance: the comparisons we did a few years ago suggested
> that parameter binding is around 40% faster for non-cached access from Zope
> as Zope does quite a lot of work to turn ZSQL methods into usable queries.
> If the caching works for you then you will have pretty good performance
> because Zope will only actually run the query for something that isn't in
> the cache. Stored procedures can offer a performance improvement if you plan
> to manipulate the data in any way, ie. if you want to get data out of
> several views and do something with it before you pass it to the browser.
> But most importantly - in the Zope world the RDBMS is unlikely ever to be
> your bottleneck.
>
> 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 our new mxODBC.Connect Python Database Interface for free ! ::::
>
>
> eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
> D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
> Registered at Amtsgericht Duesseldorf: HRB 46611
> http://www.egenix.com/company/contact/
>
>
> _______________________________________________
> Zope-DB mailing list
> Zope-DB@zope.org
> http://mail.zope.org/mailman/listinfo/zope-db
>

Am 18.02.2009, 20:24 Uhr, schrieb Allen Schmidt Sr. <aschmidt@fredericksburg.com>:

> Hey Charlie,

> What timing. I am having my server guys drop the trial egenix zope da in
> place as I write this. Seeing too many errors and oddities with the
> ancient MySQLDA we use now. Planning on converting all our mysql
> connections to the mxodbc connectors, but utilize the exact same name so
> we don't have to change every ZSQL method. Is this a sound practice? Or
> do you have any other solutions.

If MySQL is to stay as your RDBMS then this work perfectly. It's one of the nice things about Zope that you tend to overlook: some replacement components really can just be dropped in - the ZSQL methods just know the id of the connection. To be honest the 3.0 version of the ZMySQLDA isn't bad and some of the errors may be related to other things - I've recently had a project with key errors on cached ZSQL results which are independent of the DA. But I'm pretty confident that the mxODBC Zope DA is the best around but being able to access more of the underlying driver can be a real life-saver. One thing I would recommend is moving all you ZSQL to the file system if you haven't already done this.

> Thanks and sorry for bothering you directly. Please let me know if not
> appropriate.

While I don't mind the e-mail at all. It's always good to keep discussions like this on list so to that others can join in or benefit.

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 our new mxODBC.Connect Python Database Interface for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/


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

I understand. Thanks.
Did not realize there was a new version of the python/zmysqlda available. Thanks for that info. Would really like to use the mx connectors but at this point if I can use an updated free one and it works, then I need to do that.

And no, all our stuff is in the ZODB as ZSQL methods. Always has been. We do some stuff in straight python on the filesystem and some external methods but the vast majority is still done the same old way. Currently running on 2.8.9.1

Thanks for the info.

Allen


Charlie Clark wrote:

Am 18.02.2009, 20:24 Uhr, schrieb Allen Schmidt Sr. <aschmidt@fredericksburg.com>:

Hey Charlie,

What timing. I am having my server guys drop the trial egenix zope da in place as I write this. Seeing too many errors and oddities with the ancient MySQLDA we use now. Planning on converting all our mysql connections to the mxodbc connectors, but utilize the exact same name so we don't have to change every ZSQL method. Is this a sound practice? Or do you have any other solutions.

If MySQL is to stay as your RDBMS then this work perfectly. It's one of the nice things about Zope that you tend to overlook: some replacement components really can just be dropped in - the ZSQL methods just know the id of the connection. To be honest the 3.0 version of the ZMySQLDA isn't bad and some of the errors may be related to other things - I've recently had a project with key errors on cached ZSQL results which are independent of the DA. But I'm pretty confident that the mxODBC Zope DA is the best around but being able to access more of the underlying driver can be a real life-saver. One thing I would recommend is moving all you ZSQL to the file system if you haven't already done this.

Thanks and sorry for bothering you directly. Please let me know if not appropriate.

While I don't mind the e-mail at all. It's always good to keep discussions like this on list so to that others can join in or benefit. Charlie

Am 18.02.2009, 21:36 Uhr, schrieb Allen Schmidt Sr. <aschmidt@fredericksburg.com>:

> I understand. Thanks.

> Did not realize there was a new version of the python/zmysqlda
> available. Thanks for that info. Would really like to use the mx
> connectors but at this point if I can use an updated free one and it
> works, then I need to do that.

Ours works better! ;-) Which specific errors have you been experiencing?

> And no, all our stuff is in the ZODB as ZSQL methods. Always has been.
> We do some stuff in straight python on the filesystem and some external
> methods but the vast majority is still done the same old way. Currently
> running on 2.8.9.1

I think DirectoryView, FSObject and FSZSQLMethod are available separately, otherwise just get the latest version of CMFCore that will run on your version of Zope. It takes about five minutes to move a ZODB ZSQL-Method to the file system and it's worth it, especially if you can remove your dependency on ExternalMethods as well.

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 our new mxODBC.Connect Python Database Interface for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/


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

Am 19.02.2009, 01:15 Uhr, schrieb Jaroslav Lukesh <lukesh@seznam.cz>:

> Hi,
> we have moved from complicated SQL query into stored procedure with few
> temporary tables with about 100x gain. Without changing any zope code,
> except calling SQL query. SQL query cost about 40sec (SQL server time),
> now with stored procedure about 0,5sec. We was indexed tablews, but
> query sometimes reach some limits of SQL server. Now procedure does not
> reach limits and works like a charm.

Hi Jaroslav,

thanks for the info. Again, keeping this on list lets us others join in. There may indeed be cases (and views do spring to mind) where stored procedures may run a lot faster than a client query. This is a matter of implementation and in theory shouldn't happen as much with bound parameters. After all the database just parses the SQL once and can rerun the query again and again and take full advantage of its caching system. It may be that SQL server keeps the temporary tables around for longer for stored procedures since it knows to expect they will be called. Or they may be another entirely different reason. In any case such long-lived queries such as that are very good candidates for using stored procedures.

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 our new mxODBC.Connect Python Database Interface for free ! ::::


eGenix.com Software, Skills and Services GmbH Pastor-Loeh-Str.48
D-40764 Langenfeld, Germany. CEO Dipl.-Math. Marc-Andre Lemburg
Registered at Amtsgericht Duesseldorf: HRB 46611
http://www.egenix.com/company/contact/


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