Will a SP call from Java make 2 round-trips to the DB?

user12240205

We are in the process of developing a e-commerce application. It is a web site for a book shop. It is a site very similar to Amazon.com where

you can order books online.  Front end is in Java Technology.  There is however a concern about where to put the business logic.

I am suggesting to put all business logic in the Oracle Database, as stored procedures (i.e. packages).

However, one of my colleague says that when you call a Oracle stored procedure from Java,

it takes 2 round trips, one to validate the procedure and then to validate the input / ouput parameters of the procedure.

So he is saying there could be a performance issue. So his suggestion is to code all the business logic in the

middle tier in Java.

Is this true?

In a website application like what we are trying to build, is it sound advice to put all business logic in the DB?

Or should it be in the middle tier (app. server) programmed in Java? Or should you spread in between the

middle tier and DB? If so how?

jwenting

Sounds like he once built an application calling a stored procedure using very bad coding practices leading to serious performance issues.

There's no reason to have poor performance calling stored procedures. BUT, there are things that might be faster when done outside of the database, basically things that don't need data besides what's already available to Java. There the network traffic sending to and from the database takes time you don't need to spend.

End result: use the database for what the database is good at, use the Java for what Java is good at.

A major benefit of doing everything that relates to data in the database is that the database is secure from things outside of the database doing stuff that would potentially lead to data corruption (think triggers, don't replace a trigger inserting a record in an auditing table on inserting a record in a main table with a Java method doing the same thing).

Unknown

I am suggesting to put all business logic in the Oracle Database, as stored procedures (i.e. packages).
However, one of my colleague says that when you call a Oracle stored procedure from Java, 
it takes 2 round trips, one to validate the procedure and then to validate the input / ouput parameters of the procedure.
So he is saying there could be a performance issue. So his suggestion is to code all the business logic in the 
middle tier in Java.
 
Is this true?

No - there will NOT be a performance issue for properly written code.

What type of 'business logic' are you referring to? The two primary divisions for 'business logic' are often:

1. business rules that are infrequently changed or cannot be modified easily by the business user

2. business rules that might be frequently changed and can be changed easily

Consider a simple use of your e-commerce application: a user enters payment information: amount, payment method and a user discount may apply if the purchase is part of a promotion.

The validation of the amount and payment method and the types and mechanics of the discount fall into #1 above.

The rules for how and when to apply one of the predefined discounts falls into #2 above. Business users may want the freedom to modify the amounts for the discounts and some of the rules that determine when they apply.

.

Data validation should be performed in the database to minimize the risk of data corruption. If no validation is done in the database there is no way to prevent invalid data from being introduced either manually or via back-end batch processes.

You may also wish to perform some of the same validation outside the database. For example, simple data validation can be easily done in the client tier to make sure that data is the correct length, is only numeric, etc. This avoids sending invalid data to the middle-tier and then on to the database only to have it rejected; that WILL cause multiple trips to the database and those trips will interfere with the user experience.

The best place for the business rules relating to 'data' is the database.

The best place for the business rules relation to 'operational aspects' of your app (like the discount strategy) depend on your requirements. You can put code and rules that allow business users to 'modify' discount rules or data in the middle tier. The data base will STILL be used to validate that those changes do not violate the boundary conditions for that user or that data.

So a business rule that allows a business user to change the discount from 10% to 20% for a tier 1 user can be implemented in the middle tier. That doesn't involve any change to the database or data model.

jschellSomeoneStoleMyAlias

First of course is the presumption that you could put "all" the business logic in Oracle stored procedures in the first place.  If it nothing but a very thin client app over Oracle then that might make sense although even then I doubt it.  And that sort of architecture would be nothing like Amazon.

Second is the presumption that bad performance is "caused" by a technology choice.  In the vast majority of businesses poor performance is caused by bad requirements, bad architecture and bad design. A bad decision might lead to a bad technology choice but the technology itself is not at fault.

Third Amazon has a large infrastructure that one goes through before one ever even gets to a persistent store.  If your business model is realistically attempting to compete with Amazon then you need to deal with that from the start.  If this is pie in the sky then someone needs to start creating a realistic architecture before someone starts pouring millions of dollars down a black hole.

Fourth a correctly architected system with a good designs, good data models can lead to good implementations that means that one will use stored procedures in such a way that implementation details will not have any measurable impact on real requests in the system.

TPD-Opitz

The biggest advantage of having the business logic in Java is that TDD is much easier. Especially there are sophisticated mocking frame works for Java while mocking in PL/SQL is some kind of a challange.

I totally agree with the others that there are parts of your business logic should stay in database. But in general I'd prefer Java-based solution because of the better testability.

bye

TPD

jwenting

there are unit test frameworks for PL/SQL

I'd prefer to have anything dealing with inserts, updates, and deletes in the database, at least all validation and creation of child records, for a large database where the data will be manipulated by several channels. This prevents data corruption by external applications that fail to correctly implement things.

Querying may then be done by anyone if you like who has the right permissions.

TPD-Opitz

jwenting wrote:
there are unit test frameworks for PL/SQL

I know that there are UT frameworks for PL/SQL but they do not provide mocking.

It is an essencial part of unit testing in other programming languages that you replace parts of the code you currenty don't want to test but the code you're focussing on needs to interact with by mock replacements with reduced and/or easy to manipulate behavior. This is so important because it helps to implement an important rule of unit testing: a test must not fail if other code, the test does not focus on has been changed.

Example:

You have 2 packages calling procedures and functions of each other. You have unittest for both packages.

If you need to change a function in package1 how would you prevent failing of test for package2?

I don't know of a framework that can do for PL/SQL what eg. Mockito does for Java...

bye

TPD

Unknown

I know that there are UT frameworks for PL/SQL but they do not provide mocking.

Excellent point!

Certainly for Oracle there is currently no way to really do that for packaged code. And although for schema level functions/procedures you could use synonyms that would be a terrible way to try to implement that.

IMHO the main impediment is that PL/SQL doesn't have the equivalent of dynamic linking or 'make' files.

Oracle actually uses automatic recompilation of dependent objects and touts this as a 'feature'!

See this Oracle white paper from 2007 about PL/SQL Enhancements in 11g

http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-new-in-11gr1-128133.pdf

Since the dawn of time, PL/SQL programmers have enjoyed a benefit which (if

they program only in PL/SQL) they might not even have realized they’re getting:

they don’t have to think about the equivalent of a make file.

A little later in that doc you will see this 'great new enhancement'. And it is great - for PL/SQL. But it is still far behind other languages

Oracle Database 11g solves this by recording dependency information not at the
granularity of the whole object but, rather, at the granularity of the element
within the object. For example, if procedure p1 calls procedure p2 in package
Pkg, then the metadata for p1 will include the fact the dependency is upon p2
(with a specified list of formal parameters with their datatypes and modes) within
package Pkg. This means that, now, the addition of a new procedure p3 to
package Pkg will not invalidate any of the dependants of Pkg — which means in
turn that the downtime you need for application patching is reduced.

Referring to the example in that doc quote when testing 'procedure p1' there really isn't any way to 'mock' procedure p2 in package Pkg.

jwenting

remember that Oracle is old enough it predates the idea of modular development, and initially the concept of applications containing hundreds of megabytes of source code was utterly insane (and IMO it should still be, in many cases).

If your application consists of say a dozen packages of maybe a dozen SPs each, having them automatically compiled as needed is great, as you're going to just manually update them on the production server as needed from your test server (and at that time, you'd probably code directly on the production server, against a test schema, and copy the SPs between schemas).

Of course now, we write the SPs on a test machine, save them from there into a VCS, from where they're put into a ZIP or TAR file that the DBA uses to update the packages on the production machine.

jschellSomeoneStoleMyAlias

> It is an essencial part of unit testing in other programming languages that you replace parts of the code you currenty don't want to test but the code you're focussing on needs to interact with by mock replacements with reduced and/or easy to manipulate behavior.

It isn't essential.  In some situations being able to provide mocks can provide benefit but myself I prefer to allow my automated tests actually test the system.

In terms of persistent stores since one still must test the store itself doing it through the database layer is one way to do that.  Additionally attempting to simulate things like database disconnects runs into the problem that how that occurs might change.  And thus a mock will not pick it up.

TPD-Opitz

jschellSomeoneStoleMyAlias wrote:

It isn't essential.  In some situations being able to provide mocks can provide benefit but myself I prefer to allow my automated tests actually test the system.

This is in fact also an important testing scenario often called integration test, but its not unit testing.

jschellSomeoneStoleMyAlias wrote:
In terms of persistent stores since one still must test the store itself doing it through the database layer is one way to do that.  Additionally attempting to simulate things like database disconnects runs into the problem that how that occurs might change.  And thus a mock will not pick it up.

My experience is the other way around: How do you simulate a "connection loss" in an automated test without mocking the database access layer? "Host not found" might be easy, but what about expected exceptions issued by your database logic?

I do my unit testing in Java using Mokito which lets me simulate the mocked JDBC class throwing errors. (I do not test JDBC classes because they have been proven to work in so many applications).

So Yes: I do test that my piece of code deals with error conditions and this is easier than doing it without mocking.

But somehow this becomes kinda OT in this thread.

bye

TPD