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 Fri, 20 Jun 2008 07:42:05 GMT
>
> Maybe that's your hardware, operating system or JVM version?
> I was using Derby 10.4.1.3 on Solaris 10 and Java SE 6 on a dual CPU (AMD
> 2.4 GHz) machine.


I checked Derby 10.4.1.3 with JDK 5u15, JDK 6u6 and JDK 7 build 28 (latest
now).
usage 10.4.1.3 seems to be 1 second faster, but 9,5 is the best time.

switching between JDK 5 -> 6 -> 7 did not increase speed.


>
> But if 6 seconds is still too much, and you can't use another algorithm, I
> don't think I have anything more that can help you.


there are 100 algorithms, and they can not be written in pure-SQL. That is
the main idea why i write SP instead of pure-SQL.


> I don't think it is correct to blame this on Java. There are other database
> products using Java, and for all I know they might be faster for the
> specific case we are discussing.

I thought that if Java take 2 sec instead of 1 PL/SQL - java can scale up
without license payments. So Java TCO can become cheaper than Oracle.


> Also, I'm not sure how comparable the Java stored procedure code and the
> PL/SQL are.
> Could you post the latter?

pl/SQL code + timing.

set term off
CREATE OR REPLACE PROCEDURE test1 AS
    iTotalAmt   NUMBER;
/*
    можно было просто NUMBER написать, но захотелось показать
    тесную интеграцию процедурного кода и структуры
данных
*/
    iLastClient testtotals.client%TYPE;
BEGIN
    FOR cc IN ( SELECT client, order_amount
                  FROM testbig
                 ORDER BY client)
    LOOP
        If iLastClient IS NULL Then
            iLastClient := cc.Client;
            iTotalAmt   := *0*;
        End If;

        If iLastClient != cc.Client Then
            UPDATE testtotals SET
                 client_total = iTotalAmt
             WHERE client = iLastClient;
            If SQL%ROWCOUNT = *0* Then
                INSERT INTO testtotals
                       (client, client_total)
                VALUES (iLastClient, iTotalAmt);
            End If;
            iLastClient := cc.Client;
            iTotalAmt   := *0*;
        Else
            iTotalAmt := iTotalAmt + cc.order_amount;
        End If;
    END LOOP;
    COMMIT;
END;
/
-- Непосредственно тестирование, запускаем три раза
DELETE FROM testtotals WHERE client < *64*
/
COMMIT
/
set timing on term on
PROMPT Start
EXECUTE test1

set timing off term off
DELETE FROM testtotals WHERE client < *64*
/
COMMIT
/
set timing on term on
EXECUTE test1

set timing off term off
DELETE FROM testtotals WHERE client < *64*
/
COMMIT
/
set timing on term on
EXECUTE test1

set timing off term off
DROP PROCEDURE test1

/
set term on
PROMPT Done

 Code not exactly the same and it have ligical errors. But that error are
not change time.

>
> Does anyone know if (or rather how well maybe?) Oracle is capable of
> optimizing the PL/SQL?

I think tt PL/SQL are working with data on more low level (level near
storage) than Debry.
Mime
View raw message