This is the second part of my previous article, where I’ve compared PL/SQL with Java on CPU intensive tasks.
I’ll use simple task of inserting values into the table with LOB columns, calculating the length of LOB, trim the LOB and re-calculate the length again.
Although not realistic, test case is good enough to show behavior of PL/SQL and Java in data intensive tasks.
To keep space for this article under control, I’ll show you only the PL/SQL part of data intensive code that I’m trying to compare (Java code is very simple also, but consumes 200 lines which is a way too much for this article).
create table test_table
(
x varchar2(30),
b blob,
c clob
)
tablespace users
LOB ("B") store as basicfile "B" (tablespace users),
LOB ("C") STORE AS BASICFILE "C" (tablespace users);
create or replace procedure lob_tests
as
time1 timestamp;
time2 timestamp;
begin
select systimestamp into time1 from dual;
delete from test_table;
commit;
insert into test_table values ('first','01234567890123456789012345678901234567890123456789', 'one.two.three.four.five.six.seven.eight.nine');
insert into test_table values ('second','98765432109876543210987654321098765432109876543210', 'nine.eight.seven.six.five.four.three.two.one');
for rec in (select * from test_table)
loop
-- Show the original lob length
dbms_output.put_line('Show the original lob length');
dbms_output.put_line('blob.length()=' || dbms_lob.getlength(rec.b));
dbms_output.put_line('clob.length()=' || dbms_lob.getlength(rec.c));
-- Trim the lobs
dbms_output.put_line('Trim the lob to legnth = 9');
dbms_lob.trim(rec.b, 9);
dbms_lob.trim(rec.c, 9);
-- Show the lob length after trim()
dbms_output.put_line('Show the lob length after trim');
dbms_output.put_line('blob.length()=' || dbms_lob.getlength(rec.b));
dbms_output.put_line('clob.length()=' || dbms_lob.getlength(rec.c));
end loop;
commit;
select systimestamp into time2 from dual;
dbms_output.put_line('====> time2: ' || time2);
dbms_output.put_line('====> time1: ' || time1);
end;
Here are the results:
1. PL/SQL (native/interpreted) 1.5 – 2 ms
2. Java Server Side – interpreted 77 ms
3. Java Server Side – native 98 ms
4. Java Client Side 296 ms
The main points that can be concluded from the test results are:
1.
For data intensive tasks, native compilation has neglectable influence (unlike CPU intensive tasks, as can be seen from Part 1 of this article).
2.
For data intensive tasks, Java running inside of Oracle database are 4 – 5 times faster when comparing with the same Java code running from outside (Client machine or from the Middle tier).
3.
As expected, PL/SQL is by far (approx. 40 times) faster then Java code inside the Db.
Summary:
If you combine Part 1 of this article with this tests, I hope you can easily decide when to use some feature.
a)
For data intensive tasks, PL/SQL is by far the best choice.
b)
For CPU intensive tasks, Java inside the DB with native compile option is the best choice only if you have enough CPU resources on Db server and/or when you are fetching a large amount of data for processing (see point 2, where the only reason why Java in Db wins for data intensive tasks is because it doesn’t have to move data around across the network).
Otherwise it’s better to do CPU intensive tasks on Application server (middle tier) or client. PL/SQL is the last option.
Keep in mind that everything can be improved, but with additional cost of development time and effort.
In case of CPU intensive task, I could create external C procedure and call it from the PL/SQL block of code for example, but this wouldn’t be PL/SQL.
This comparison is targeting on what each language has to offer by default, not on how to improve it some programming language.
Comments