File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Should I use or avoid Stored procedures? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Should I use or avoid Stored procedures?" Watch "Should I use or avoid Stored procedures?" New topic

Should I use or avoid Stored procedures?

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 751
I know they make your queries significantly faster, but what are the pros and cons of using them? Thanks!
[ June 19, 2006: Message edited by: Timothy Sam ]

SCJP 1.5
Ulf Dittmer

Joined: Mar 22, 2005
Posts: 42965
Stored procs ususally are faster, but be sure to perform relevant timings before just assuming that they will in your case.

Cross-platform compatibiliy is another concern. If you need to support different dabatases then an approach involvin stored procs is harder to maintain than one that only uses standard SQL.

Stored procs have (IMO) the drawback that business logic has a tendency to creep into them in the name of convenience or performance. That is ususally not a good idea.
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 751
Thanks! I just wanted to be sure of it. It seems to me that when my app needs to change frequently, it's not advisable to put the business logic in my db. After having read wikipedia, it became more clear to me their pros and cons. I guess I'll be sticking with my normal queries or perhaps only use stored procedure on parts of my program that doesn't need frequent change and doesn't involve too much logic...
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

Generally you avoid using them as much as possible because they are rarely portable. Its better to use a development-side solution whenever possible such as querying for more data than you require, and performing some filtering afterwards.

The limit though, is that once in awhile these operations become extremely costly on network bandwidth (for example, if you require the entire database to process the query), database processign power (too many joins), or application server processing power (too much post-processing of the query), such that stored precedures really are required to improve efficiency.

Even then, you should try to make them as simple and general as possible so that you can still gain the performance advantage without sacraficing too much portability.

[OCA 8 Book] [Blog]
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 751
Until how many joins, until I use a stored procedure do you recommend?
I agree. Here's the link:
subject: Should I use or avoid Stored procedures?
jQuery in Action, 3rd edition