Why Stored Procedure and Why not...

1.     Why Stored Procedure?

a)      Make Database as a Service (DB API), to wrap the brittle database with additional layers in an attempt to find loose coupling. It provides an extra level of abstraction from the underlying database schema, hiding users, hiding details.

b)      Good way to maintain the logic relationship between tables or common businesses (migrated triggers, checked constraints).

c)       Stored Procedure is the only way/Language that will last as long as the data.

d)      Less DB connection hits needed, with better performance at most of the time. (SP cache, pre-compile, Optimizer).

e)      Stored Procedure has better security control.

f)       Stored Procedure has better maintenance in deployed environment. Changing Logic code without restart the services.

g)      Stored procedures can reduce network traffic. It’s appropriate to deal with fetching and looping a great quantity of data.

 

2.     Why not Stored Procedure?

a)      Need good data model / data architecture/ DBA, and the stored procedure should be written in a maintainable way.

b)      Not easy to change and test

c)       Modularization of Stored Procedure is not very easy

d)      Stored Procedure don't provide much feedback when things go wrong

e)      Stored Procedure is not object-oriented language, it can't pass objects or express objects

f)       Not easy for customer to change Business Logic by themselves

g)      Version Control is not available

h)      With not good extensibility of Computing Capability, at most of the time, multiple applications servers connect to one database server.

i)        If Business Logic or computing consumes much time, the table will be locked all the time.

 

3.     Performance Test

The drawing below shows the performance comparison between Java and Oracle. Loop is used everywhere in the Oracle and Java, here in the loop, just did something like assignment “Variable = Value_From_Loop”, we can see the Oracle Loop has better performance than Java.

 

4.     Conclusion

a, we can know that the oracle performance is better than java most of the time while performing the same thing

b, we also know that the Oracle is expensive than Java.

So, make decision by yourself