Am 19.02.2009, 01:15 Uhr, schrieb Jaroslav Lukesh <>:

> 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 

Charlie Clark

Professional Python Services directly from the Source
>>> Python/Zope Consulting and Support ...
>>> mxODBC.Zope.Database.Adapter ...   
>>> mxODBC, mxDateTime, mxTextTools ...

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

Zope-DB mailing list

Reply via email to