[prev in list] [next in list] [prev in thread] [next in thread] 

List:       zope-db
Subject:    Re: [Zope-DB] [Zope] Stored Procedures Versus ZSQL Methods
From:       "Charlie Clark" <charlie () egenix ! com>
Date:       2009-02-19 19:41:52
Message-ID: op.upl6b200y861uf () fuchsia ! local
[Download RAW message or body]

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


[prev in list] [next in list] [prev in thread] [next in thread]