CODEWITHSUNDEEP
javaperformanceoracle-databaseplsql
komedit1
komedit1

Reputation: 255

Performance overhead of using java in oracle

Greetings everybody,

I came across the feature of using java in oracle, a few days back. Ever since I am wondering about the possibility of writing static methods in java replacing regular PL/SQL logic.

As I have reasonably adequate experience with java plus the rich libraries it offers, I am tempted to write java methods instead of regular PL/SQL. Would this be a good practice?. Will there be much performance overhead in doing so?. Thanks in advance.

Upvotes: 3

Views: 2243

Answers (3)

zep
zep

Reputation: 1151

It depends! They are two languages for different purposes!

What kind of operations do you want to do?

  • Every operations that touches data layer(I know it's debatable)
  • Massive Sql operations with large data processing( bulk operations)
  • To manage any kind of transaction
  • The job can be easily done in Pl/Sql
  • Are you writing code that is strictly tied to Sql data types?
  • Monitoring the relations between the code and dependent db objects

In this case, I believe Pl/SQL is the best choice for performance.

Attention, I wrote PL/SQL:

  • PL as procedure language(2nd choice)
  • SQL as data query language(1st choice)

The power of this two languages, strictly tied in Oracle, let you write application which access data in Oracle in faster and easier way than in any other language.

Do you need:

  • Operating system operations
  • Directory and file operations
  • Massive mails operations
  • Network operations
  • Other things you can't do in:
    • SQL (1st choice)
    • PL (2nd choice)
  • write code to reuse in architecture like Enterprise Java Beans
  • Write code to reuse elsewhere

In this case I think Java is a better choice, 3rd choice in Oracle.

But I'm not reinventing the wheel, this information are largely accessible and proved by Oracle experts like Tom Kyte etc.

Just some useful link to search:

Upvotes: 6

HamoriZ
HamoriZ

Reputation: 2438

I think it can be acceptable if you store the CRUD operations as stored procs. If you use any ORM framework, then you might map the SPs. It is more usual that the database is the more stable part of the application. The application might be refactored or replaced, but the database does not change (it is just my experience).

Upvotes: 2

HJW
HJW

Reputation: 23443

There will be overheads involved in your decision to walk away from PL/SQL into Java code.

Although i am not a big believer of putting business logic into PL/SQL, i have seen just too many companies doing that, including my own.

The "performance consideration" of doing this does not come as whether or not to use static methods. For example, you may need to declare an arraylist to sort the values, and depending on the values, retrieve more results from the database in another query.

IMO, i would put business logic in my application, and not invest in PL/SQL. This also helps to ensure DBMS portability and not buy-in.

Upvotes: 10

Related Questions