wood burning stoves 2.0*
The moose likes JDBC 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
Bookmark "Should I use or avoid Stored procedures?" Watch "Should I use or avoid Stored procedures?" New topic
Author

Should I use or avoid Stored procedures?

Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
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
http://devpinoy.org/blogs/lamia/ - http://everypesocounts.com/
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 42035
    
  64
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.


Ping & DNS - my free Android networking tools app
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
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
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Timothy Sam
Ranch Hand

Joined: Sep 18, 2005
Posts: 746
Until how many joins, until I use a stored procedure do you recommend?
 
GeeCON Prague 2014
 
subject: Should I use or avoid Stored procedures?