[elephant-devel] Stored procedures

[Ian Eslick]

It's interesting that there is only a little performance advantage.   
Have you done some profiling to see where the time is going?  It  
sounds like either the queries were simple enough that the  
compilation step was trivial or that we're seeing Ahmdal's law and  
the SQL costs are swamped by some other activity.

On Apr 11, 2007, at 4:30 AM, Henrik Hjelte wrote:

> 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
>
> _______________________________________________
> elephant-devel site list
> elephant-devel at common-lisp.net
> http://common-lisp.net/mailman/listinfo/elephant-devel