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.
Joined: Sep 18, 2005
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...
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.