Hello.
2008/6/11 Øystein Grøvlen <[EMAIL PROTECTED]>:
> 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?
