>
> 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.
