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