Disadvantage of Java Business Developmet over Stored prcedures

[G.Sathish kumar]

Hi

I have a doubt like where to develope Business logic specific to the project. is that better to implement in java Service layer or stored procedure?

i feet it is better to keep in java because of issues clustering, long time process(lock with tables), some of dynamic itererative process not posible in stored proces(i implementing statistical model intially through stored procedure then moved to java because of complexity)

if i am wrong anywhere please let me know and please let me know anyothere points to add

[James Ward]

It is better to keep Business Logic in Java.
Stored Procedures, should be used for database specific operations.

[G.Sathish kumar]

James Ward wrote:It is better to keep Business Logic in Java.
Stored Procedures, should be used for database specific operations.

my friend who is working db developement telling that better to keep in stored procedures because of if any changes in db we can easily change in the code and moreover the locking issue are solvable

please let me know what are the point i can talk with him abou java is best to implement Business development

[manoj r patil]

I think this is design decision whether to keep the code at middle layer or at EIS layer. There can be multiple aspects based on which the decision may vary. Some of them can be -

1. Which database are you using? Is it fast & sturdy enough to support complex SP/function jobs?
2. If multiple databases, is it heterogeneous environment? If yes, then the life becomes more challenging and you may have to split the code on db and java layers
3. If its a middleware product which should support on multiple databases, java code would be preferred as less portability support.
4. Do you want to leverage the database power when you are foreseeing more load on your app server?

But then as mentioned, this is purely design decision and I don't think there would be clear answer to it. Today when log of enterprises are going for SOA based designs, designer's role is becoming more challenging and he will have to think of coming up with the architecture which will have minimal impact on the existing services but it can very efficiently talk to each service with proper data exchange.

HTH

[John Bengler]

Hi,

I used to code business logic in stored procedures for many years and went to Java business logic some years ago.

When I changed I had many doubts, especially regarding performance and flexibility...

Now I definitely prefer writing complex business logic in Java, if you're doing it right it is better maintainable, the lack of flexibility can be an advantage, since due to the ease of access the PL/SQL code always tended to diverge between the different installations.

The performance issue didn't come up as hard as expected, at least when I moved from EJB2 to EJB3. But for massive data operations it will make sense to do this in stored procedures.

What I wouldn't do is distributing the logic for a single use case between Java and PL/SQL..


John

[G.Sathish kumar]

patil manoj wrote:
1. Which database are you using? Is it fast & sturdy enough to support complex SP/function jobs?

sql server 2005 and server is highly configured

patil manoj wrote:
2. If multiple databases, is it heterogeneous environment? If yes, then the life becomes more challenging and you may have to split the code on db and java layers

my environment is nto hetrogeneous but on hetrogeniuous environment for example parenet is oracle and childs are sql server and mysql so what ever oracle receive as change will reflect to other childs so i feel here which scenorio does we have problem to change BD to java

patil manoj wrote:
3. If its a middleware product which should support on multiple databases, java code would be preferred as less portability support.

in this situation what problem will come can you please explain because my db developer telling no problem



and i able to understand and i accept the soa impact but fundamental is important so if you elaborate your point which could be helpful

[G.Sathish kumar]

John Bengler wrote:Hi,
Now I definitely prefer writing complex business logic in Java, if you're doing it right it is better maintainable, the lack of flexibility can be an advantage, since due to the ease of access the PL/SQL code always tended to diverge between the different installations.

what could be the advantage when using busines logic in java instead of stored procedures please explain bit elaborate could be helpful

[Ulf Dittmer]

G.Sathish kumar wrote:my friend who is working db developement

He wouldn't by any chance be biased, would he :-)

telling that better to keep in stored procedures because of if any changes in db we can easily change in the code and moreover the locking issue are solvable

This doesn't make sense to me. What's the difference between changing Java code and changing stored procs if there are changes in the DB? Also, what is the "locking issue" that can't be addressed by Java code?

I think it's much better to avoid stored procs whenever possible and move the business logic to the business layer - which is not part of the DB. One of the benefits of doing that is that it's readily available for other purposes and/or data stores if necessary - accessing the DB just to execute certain business logic seems a weird way of doing things.

[John Bengler]

Hmm, some more details... o.k.

The only possibility to organize my code in PL/SQL (I think it will be the same for TSQL) is to use packages. In Java you can also use packages to organize your code, but packages can have subpackages and classes are also used to keep code together, which belongs together..

-> So Java has a clear advantage in organizing/structuring code...


In Java you usually program against Interfaces, the Implementation behind the Interface can be changed (e.g. by Dependency Injection) at Runtime. One could argue that a package header is a kind of an interface for your package and you program against the package header, not the body. One could also argue that by using Dynamic SQL the implementation could also be changed at runtime in PL/SQL. But this isn't the same and you will never have the flexibility of Java in PL/SQL.


Another Issue is the way your code will be called. If you ever have tried to publish a Webservice in PL/SQL and in Java you will see the difference. And if you want to consume a Webservice it's nearly the same (yes, it works well in PL/SQL, too, but with some severe limitations, like only one SOAP style was supported, I don't remeber if it was RPC or document). I have to admit that I did these tests with Oracle9i, maybe the handling improved, since Webservices get more and more important nowadays.

But it'S not just Webservices, in Java I can call the logic via RMI, via IIOP, via Webservice... You could say that if I have to call my PL/SQL logic I could use ODBC, JDBC, ADO, DAO,... But that's not the same... Additionally I encountered much more problems when calling PL/SQL logic with different versions of the database and especially of the driver used to connect. Some times CLOB/BLOB fields were cut after 32k, sometimes Timestamps didn't work, and so on.

Of course potential problems are inherent to every interface , escpecially between different technologies. I also had some hard times when I first used IIOP and yes,we had to fix the CLOB truncation problem anyway, but IMO it's worse with the different techniques to connect to the DB.

-> IMO there is a clear advantage in the flexibilty of accessing the logic and especially if your logic has to access other logic if you use Java


Another point that I've encountered is that it'S much easier to code in a team if you have hundreds/thousands of classes than it is if you have some ten PL/SQL packages, since the risk of a collision is lower.

Maybe your project is small and you're the only one who works on it so you're thinking that this doesn't apply in your case, but projects may grow and then require a full team.


About the diverging code between single installations:

With the logic written in PL/SQL one can easily change the logic on a productive installation, either to add a feature that is only applicble to this installation or - more common - to fix an urgent bug. Especially the latter one is a real advantage of PL/SQL - at least at the moment.

The problem is that it requires a severe discipline to merge back those code changes to your development environment or it requires the same discipline to forbid changesat productive systems,especially when a complete installation isn't running, every minute downtime costs lots of money and you know that the bug fix is just a one liner which you could do in 5 seconds. In Java you have to compile it anyway, so you have to do it in your development environment right from the start and deploy your new jar/ear to the production machine. If it's not so easy (yes, I know it's possible, but in PL/SQL it's much easier) to do a quick fix nobody can force you to do it.


An additonal issue is automatic building/testing. I've seen much solutions for Java, including mockups for datasources, etc. (like JUnit, DBUnit,..) but I've never seen anything like this in real operation for PL/SQL. There is SQLUnit and three years ago I had a short look on it, but then it didn't look user friendly. I just visited the homepage SQLUnit and it looks much better now.



Having listed lot's of issues which in my opinion are obvious advantages of using Java I have to make some concessions in favor of PL/SQL

- there is usually far less overhead, especially for small projects
- if you are not experienced in Java it may be a steep learning curve with some severe drawbacks untill you have your first application ready for operation
- if you have to handle lots of data in very limited time: the overall performance of good PL/SQL is higher than the one of Java code if data has to be retrieved/written very often (like SQL has an advantage over PL/SQL)

And a last comment: all this is just my point of view. Feel free to agree/disagree with every single point. Just think about them for a moment.


I hope this was elaborate enough And as said before: Feel free to agree/disagree with every single point.



John

[G.Sathish kumar]

Hi John

I can't disagree your valuable points and am clear now.

Some of my points i have collected

Stored procedures tough to debug

Testing is not better as you mentioned

adhoc(inline) queries when we use prepared statement is compile the query(query execution plan is ready and it generated on first time accessed and cached) and ready to use so it is one advantage

stored procedures reduce traffic

management require another one resource for development


i conclude like the design decison is better to implement in java

[John Bengler]

I also agree to all of your points...

John