In my opinion it all comes down to where you want to slog through the data. A stored procedure works within the database and can manipulate the data to return only the data that is needed. During this process various functions, calculations, and other data parts can be gathered and returned to the calling application. Sometimes the SP can do things and access information that cannot be done with the calling client.
The calling client if it wants to slog through all the data may require a much large dataset to be returned than with a SP. This may negatively impact message throughput on the system. You have to take into consideration that with a SP that module can be used by several applications. Once you have it working others can use it without concern of the inner workings. You also only have to make a change in one place to affect the change across the board. The downside is that the change may not be expected by the other applications. When there are problems you have to look in two places to determine the issue. The SP and the application code. If everything is in the application you only have to look in one place. But then you may also have duplicate code in multiple applications (that can be mitigated with includes or modules). Lastly is performance. With a SP the workload is on the database engine. That may become a bottleneck for performance. If the application is doing the data manipulation then the workload can be spread among more than one machine. With those considerations it may come down to what you prefer. I am not a DB expert and having to work with SP's and the application code involves working with slightly different rules. I instead prefer to work within a single set of coding rules and let the application do all the work with the DB merely being a storehouse. The choice is ultimately yours and there is no right or wrong decision. Ray Thompson Tau Beta Pi - www.tbp.org The Engineering Honor Society 865-546-4578 > -----Original Message----- > From: Matt Blatchley [mailto:m...@bridgeleaf.com] > Sent: Tuesday, January 05, 2010 9:59 AM > To: sql > Subject: Should I use a Stored Procedure? > > > I recently purchased O'Reilly's book, "MySQL Stored Procedure > Programming" and found some interesting paragraph about half way > through the book (yes I skipped a lot of it to get to that section). > I'm not totally new to Stored Procedure's (certainly not an expert), > but I rarely use them, which brings me to my question(s). > > I'm in the process of launching a rather large application using CF7 > and mySQL5. The application compiles a lot of metric data obtained > from marketing accounts like Adwords, Bing, Yahoo as well as > Analytical data from other software sources. > > I've got pretty much everything done with workflow and I'm in the > process of Optimizing the pages using Query Caching and Indexing of > some of the tables. I'm looking into the use of Stored Procedures and > can't seem to decide. I have specific pages that use CFChart and > supply many calculated metrics under that chart to display marketing > trends to deliver an overall view of monthly, yearly, and even daily > information. Would a Stored Procedure be better or faster in this > case?? I've been reading that there are two schools of thought on the > subject. Either use SP's all the time or don't. But that was written > in 2006, am I missing somthing?? Any thought's, insights, or opinions > would be greatly appreciated! > > Thanks, > > Matt > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Want to reach the ColdFusion community with something they want? Let them know on the House of Fusion mailing lists Archive: http://www.houseoffusion.com/groups/sql/message.cfm/messageid:3261 Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
