Money computations, comparison between java and Oracle/PL SQL which to use

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:

开发者_C百科
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.

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.

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.

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.