7

Guys can you give a good comparison regarding Oracle/PL SQL and Java in terms of their strengths and weaknesses when handling monetary computations. If you were to develop an application which will be handling a lot of money computations which of the two would you use and why?

This question is not to spark a debate between oracle/pl sql and java enthusiasts I just want to know what is considered the best practice or standard approach for this kind of requirement and the reason behind.

Scenario would be:

  1. Data would be from the database (Oracle 10g minimum).
  2. The program would compute and generate invoices based on aggregated data (100k-1M) records
  3. Business rules are very complex
  4. Business rules may change at least once a month
  5. Several reference tables will be used in the computation
  6. Program will be ran once a day

Thanks in advance.

royjavelosa
  • 2,048
  • 6
  • 30
  • 46
  • 4
    IMO that's not enough info to make an architecture decision. With these requirements you can make good solutions both in Java and in PL/SQL... – Lukas Eder Aug 24 '11 at 07:05
  • with this scenario I would implement most of the logic in the DB, you can use both (Java and PL/SQL) within the Oracle DB for this to write Stored function/Procedures etc. – Yahia Aug 24 '11 at 07:06
  • @Yahia: You would use both Java and PL/SQL and do most of the logic in the DB. Does this mean that you would use Java for modules that will handle monetary computations? Why not use PL/SQL for the computation part too? Is Java more capable/accurate/efficient for money computations than PL/SQL? Thanks. – royjavelosa Aug 24 '11 at 07:28
  • use of Java is not because of money computations - these can be done in both... but since you say it is really complex Java is easier to structure well... you can achieve it all in PL/SQL (which I personally prefer) but I don't know how proficient you are in PL/SQL – Yahia Aug 24 '11 at 07:31
  • @Lukas Eder: What are the additional infos you need to come up with an architecture decision? – royjavelosa Aug 24 '11 at 07:31
  • @royjavelosa: There are lots. Important ones are: Developer experience in both environments, testing/productive access availability to both environments (easy to deploy PL/SQL, Java?), and many detail business questions. And of course, the tools you use. Plain JDBC? Hibernate? Or third party tools facilitating access to PL/SQL? – Lukas Eder Aug 24 '11 at 07:43
  • @yahia: You also dont know how proficient i am in java :) So lets just assume that the team that will handle the project is proficient both in java and pl/sql. In terms of maintainability of the code I agree with you that Java is easier to structure. Thanks – royjavelosa Aug 24 '11 at 07:43
  • you are right - that's just my knowing more people proficient in Java than in PL/SQL, but this is defnitely not something I can generalize :-) – Yahia Aug 24 '11 at 07:50

3 Answers3

8

These criteria definitely favour the use of PL/SQL.

1) Data would be from the database (Oracle 10g minimum).
2) The program would compute and generate invoices based on aggregated data (100k-1M) records
5) Several reference tables will be used in the computation
6) Program will be ran once a day

If it's in the database and involves data, especially lots of data, then PL/SQL is the default option.

The remaining criteria are trickier to evaluate:

3) Business rules are very complex
4) Business rules may change at least once a month

PL/SQL is definitely capable of undertaking computation, and it has lots of built-in arithmetic functions. So it comes down to exactly what you mean by "business rules" and "complex". We can code complex business logic in PL/SQL (I have done it) but Java has language features which undoubtedly make the task easier: I'm thinking of stuff like reflection and introspection.

Perhaps you are thinking about using a Rule Engine? Certainly, the financial services sector has been a major user of Rule Engines. And abstracting the rule sets from the processing would make it easier to satisfy the fourth criterion.

Java has two established Rule Engines, Drools and JESS. There is a Rule Engine in PL/SQL, and has been for some time, but it is mainly used as part of Oracle Streams. However, the API is exposed so it could be used for other purposes. Find out more.

If you're thinking about Rule Engines then I think that tips the hand towards Java. Business Rules Processing is a specialist programming paradigm, and there is simply more experience and support for it in Java than PL/SQL. I suspect that means going for Java in the middle tier rather than Java Stored Procedures, which has implications for network traffic and performance.

Community
  • 1
  • 1
APC
  • 144,005
  • 19
  • 170
  • 281
  • Yes rule engines are what I have in mind. Criteria 1,2,5,6 tells me to use pl/sql but 3,4 is suited for Java. Another point is maintainance, it just seems more easy to maintain and enhance java code for changing business rules. – royjavelosa Aug 24 '11 at 13:00
5

I think the most important aspect you should consider is "keeping your code DRY". You should at all costs avoid duplicating your complex business rules to both Java and PL/SQL. Having said this, you are likely to prefer putting most of it in PL/SQL, because then it will be available in procedures, views, etc when you actually need it.

In any case, for performance reasons, you are probably going to perform "data aggregation" in the database considering the complexity of tasks applied to a medium-sized amount of data. So if you actually need business rules for aggregation, they're already available in the database. No round-trip to Java is needed.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
  • Data Aggregation will be performed most likely before the money computation part so data aggregation is not really my concern. My concern is the actual part where you apply complex rules to the aggregated data to compute for rates, discounts, and tax. In Java you have the option to use rule engines to simplify things also in Java there are a set of established best practices in handling money related computations. Are there pl/sql standards in handling money computations? I asked because I am also considering to use pl/sql 100% but the lack of best practices bothers me. – royjavelosa Aug 26 '11 at 08:36
  • @royjavelosa, We're doing quite a bit of PL/SQL money/rate calculations ourselves for our banking software. When you look at Oracle's data warehousing examples in the documentation (`CUBE`, `ROLLUP`, `CONNECT BY`, `Common Table Expressions`, etc), they're often about accounting and banking, too. So PL/SQL is perfectly well-suited for the task. The problem is that the PL/SQL world is much more closed than the Java world. That means that access to "best practices" is much harder or costlier.. On the other hand, Oracle stored procedures support Java. Maybe you can run a Java rule-engine in the DB – Lukas Eder Aug 26 '11 at 09:02
3

You are facing some interesting trade-offs here.

Complex, changing, business logic? That places a premium on program structure. OO techniques are surely applicable. Hence Java seems a good fit. Doing serious processing client side may also help with testing and versioning which will also be important.

But, bringing back a million rows to work on? That's going to be a performance challenge.

With discipline you can produce and maintain well-structured PL/SQL. My observation is that all too often folks end up doing reuse by cut-and-paste, over time structure is lost. I don't know what the current state of IDEs for PL/SQL, maybe these days there are tools to help with refactoring. For sure in the Java world the refactoring tools are pretty good, so the barrier to keeping good structure is low.

My ideal scenario would be that some set of queries across the million rows would result in small sets of data that need complex munging, and we can do that munging in Java. So SQL for getting the data, Java for processing it - hit the sweet spot for both without paying excessive data transfer performance penalties. We don't know enough about your requirements to know if that's possible.

djna
  • 54,992
  • 14
  • 74
  • 117
  • 2
    implement most in Java but don't execute it client-side - just import it into the Oracle DB server and execute it there, combine that with som PL/SQL (where it fits better)... you get the best of both worlds... with maximum performance since nothing except the params and the end-result goes over the wire – Yahia Aug 24 '11 at 07:23
  • @yahia, I agree, this seems like the way forward. – djna Aug 24 '11 at 07:36
  • Whether or not you refactor is primarily a matter of attitude. If you have the attitude you can also refactor with `vi`, because your code is already "refactorable" – Lukas Eder Aug 24 '11 at 07:38
  • 2
    Well, when the tool goes "Extract Method" and susses out the parameters it sure makes life easy. My model is that folks lie on a "guilt-laziness" spectrum, the tools reduce the effort and so adjust the balance in the favour of goodness. Doesn't help if we feel no guilt about bad code though :-) – djna Aug 24 '11 at 07:45