Solved

cfquery or Stored Proc

Posted on 2014-03-17
2
155 Views
Last Modified: 2014-03-17
Experts-

Was wondering if you would recommend having the SQL queries being called as stored procs through a cfc or would you recommend writing cfqueries within the CFC instead

Nick
0
Comment
Question by:nmarano
2 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39934289
It really depends.  There are reasons for both. Personally, my preference is stored procedures:

1) Some shops prefer to do ALL queries through stored procs to keep the logic centralized in the db. It's easier to test/tune queries.
2) Using stored procs also makes it easier to centralize permissions

However, honestly for simple queries, all optimized with cfqueryparam, there is not a big difference performance wise.  One case it can make a big difference is with complex sql. Obviously stored procs offer greater features/functionality (table variable, etc...). What might often require multiple queries and complex CF code, can often be achieved more efficiently within a stored proc, by taking advantage of temp tables and set based operations. In addition performing multiple statements within a stored proc only requires one call to the db, saving some network o/h on the CF side.

But again, if you're simply talking about single statement queries - its usually 6 of one, half-dozen of the other IMO.
0
 
LVL 2

Author Closing Comment

by:nmarano
ID: 39934310
Thank you sir for the feedback
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now