[elephant-devel] Stored procedures

[Henrik Hjelte]

Regarding stored procedures, I agree with Ian that the main performance
advantage that come from them is that the query planning is prepared in
advance. This is also done if you use prepared sql statements, so they
give the same advantage. Stored procedures can however be faster if they
involve several steps, then you won't have to send intermediate results
to the client and then back to the server. What you should avoid for
performance reasons is repeatedly sending strings to parse and execute. 

I have really tried to optimize the postmodern backend for speed, still
it is slower than BerkeleyDB. The postmodern backend uses prepared
statements for almost everything "simple", I could not measure any
performance advantage with using stored procedures for this. There is
one stored procedure left because it involves several steps, so in
theory it can be faster (compared to a couple of prepared statements),
but I haven't actually measured if and how much faster.

Negative: stored procedures for the clsql backend will definitely remove
portability between databases. Positive: a little faster. But I am
totally convinced that stored procedures will not bring clsql even close
to the performance of BerkeleyDB.

/Henrik Hjelte


On Tue, 2007-04-03 at 19:31 +0200, Pierre THIERRY wrote:
> Scribit Robert L. Read dies 03/04/2007 hora 11:08:
> > Stored procedures tend to not be very portable; therefore to put them
> > in the current "postgres" backend, which should really be called a
> > "clsql" backend, would make it less likely to work with MySQL.
> 
> I was thinking at having some PostgreSQL-specific bits within the clsql
> backend. That would apply to MySQL or any other DB that can use stored
> procedures to make some queries faster.
> 
> > However, this raises and interesting question:  Is performance a
> > significant problem (at least for the Postgres users?)  If you had a
> > "wish list" for Elephant features, would better performance be at the
> > top?
> 
> I just don't want to be limiting. The only way to go seemed to me to be
> to benchmark various uses of stored procedures. On the other hand,
> having a cache for read queries, as was discussed earlier, could well
> make the stored procedure useless. Or not. Well, we need to measure.
> 
> Doubtfully,
> Pierre
> _______________________________________________
> elephant-devel site list
> elephant-devel at common-lisp.net
> http://common-lisp.net/mailman/listinfo/elephant-devel