[Zope-DB] [Zope] Stored Procedures Versus ZSQL Methods

[Charlie Clark]

Am 19.02.2009, 01:15 Uhr, schrieb Jaroslav Lukesh <lukesh at 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/