db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "vodarus vodarus" <voda...@gmail.com>
Subject Re: Speed of using Derby DB
Date Wed, 11 Jun 2008 11:56:43 GMT
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?
Mime
View raw message