Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

call PL/SQL SP vs. call directly(without PL/SQL SP)?

I wrote a rmi application to test the performance of calling PL/SQL SP using java. at the rmi server side, I called a PL/SQL SP. I found the speed was 10 times  slower than directly call by JDBC. anybody knows the reason? thanks.
0
lisayan
Asked:
lisayan
1 Solution
 
iarlaCommented:
There is a lot of overhead with RMI. There are a number of layer including

stub skeleton layer,remote reference layer
and the transport layer.

These layers perform marshalling, holding remote references

RMI was primarly designed for development of distributed object which JDBC was designed for database access.
0
 
soraCommented:
Let me understand the questions;

Trial 1: JDBC remote client direct to a db server
Trial 2: RMI client to a RMI Server (located on the same system as the DB Server). and then RMI Server to the db server using JDBC

Am I right?

When you use RMI, I guess it works like this:

1. Your client app makes a call to the RMI server
2. The RMI server, then executes the stored procedure
3. The RMI server returns the values to the client by serializing the objects into byte stream (which is a costly operation)

Also every rmi call from the client to the server has an overhead

You can try timing it:

Does the RMI server use jdbc to communicate to the db server? Is the rmi server ON the database server?

Try to print times in the rmi server, just before it makes the stored proc call and just after it returns. You will most likely find that the time taken is more due to the extra overhead of rmi client and rmi server passing objects back and forth by serializing them


sora
0
 
lisayanAuthor Commented:
sorry for my english. the fact is that:
  (note: rmi server is on the db server)
  TRIAL 1: rmi server call db server by PL/SQL SP(such as {call getModuleName(?,?)})
  TRIAL 2: rmi server call db server by JDBC directly(such as "SELECT MODULE_NAME FROM RPMODULE")

(at rmi server side, those two trials got a simple same result by calling db server, and they should have same cost passing this result from rmi server to rmi client.So we don't care about the rmi overhead)

  at TRAIL 1,rmi server returns a result(such as a string) to rmi client cost 100ms.
  at TRAIL 2,rmi server returns same result to rmi client cost 10ms.
 
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
soraCommented:
If the objects being passed and their sizes are the same for both the trials, then there should not be any difference in timings. Since in both cases you are using RMI to pass results to client, right?

Is this always consistent? or could be due to network latency etc?


sora
0
 
mallyhCommented:
Hi there,
I could not get to grips with RMI when I
wanted to do the same as it is such a pain
every time you add a new method and keeping
the rmi registrys running, hence I went to
TGMF:
In 5 minutes you can replace your RMI stuff
with it (just for the experiment), I would
be really interested if your results improve
(only if you have the time to try this)
http://rrwnt2.uni-regensburg.de/iwi_alt/forsch/TGMF/

Greetings

Mallyh
0
 
lisayanAuthor Commented:
I mean the different is on the server side(rmi server call db server using different approach).it is none of the business of passing result to client.
0
 
abhay_djsCommented:
Hi lisayan,
THe time difference you have noted is not because of the RMI only. But is it because of the execution of SP rather than single query.
Database stores the SP in the compiled format. SO it gets executed fast.
U can try out executing the same thing on database only without using any java. What u can do is... execute simple query and note the timing. ANd then put the same query in the SP and execute that Sp and note the timing.
You can see the difference in the execution timing.

Thanks
Abhay
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now