One thing I like about stored procedures is the code reuse and the fact that
I do not have to use the search through code to find the query I need.
Second reason I use SP is because they allow me to push much of the
processing back on to the db server and speed up your application.
The third reason is that I can bring back multiple result sets with a SP
Example: ------------------
SELECT *
FROM a
SELECT *
FROM B
----------------------------
If I did this in CF I would be making two calls to the DB server. But in a
SP I make one call and bring back both results.
For charting this means that I can bring back subgroups or detailed
information to us in my charts.
Finally, I use SP because the db compiles the code and will allow it to run
faster.
You can mix your CF queries and stored procedures but abstract the code into
a cfc to make your life easier in the long run.
Hope this helps.
-----Original Message-----
From: Matt Blatchley [mailto:[email protected]]
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:3262
Subscription: http://www.houseoffusion.com/groups/sql/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
