Hello.

2008/6/11 Øystein Grøvlen <Oystein.Grovlen@sun.com>:
vodarus vodarus wrote:
it takes up to 12 seconds to calculate TESTTOTALS. Oracle PL/SQL procedure with the same algorithm need 1,5 second.

*How can i improve performance? Or Derby is so slow because of Java / JVM issues???*

Thanks.

I do not have any experience with performance of stored procedures, but note that Derby has a pretty high overhead for query compilation since it compiles queries directly into Java byte code.  Hence, comparison on single executions of statements will usual not be in favor of Derby. However, if same statements are executed many times efficiency improves (both due to reuse of compiled plans and hot-spot compilation of the Java code).

I run stored procedure for many times:
for (int i = 0; i < 5; i++) {
                s = conn.createStatement();

                sql = "DELETE FROM testtotals";
                s.execute(sql);

                begin = new Date();
                sql = "CALL calculateTotal()";
                s.execute(sql);
                end = new Date();
                System.out.println("Timing " + (end.getTime() - begin.getTime()));
                conn.commit();
                System.out.println("Committed the transaction");
            }


For you particular problem, why use stored procedures when you can do the same with the following query:

insert into testtotals
select client, sum(order_amount) from testbig group by client;

Because it's just example. There should be any function that can not be replased with pure-SQL. of course SQL can be used in this test, but test purpose is "check speed of server-side data analysing with big amount of data".

And what tools can you use to analyse big amount of relation-data? SQL can not be used in many cases. So people usually use stored procedures in RDBMS - PL/SQL in Oracle. I have idea: replase Oracle with Java application. And that Java application have RDBMS inside (like Derby in Embedded mode).


PS does derby / javaDB have it's own forum?