Solved

cfquery or Stored Proc

Posted on 2014-03-17
2
158 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create array from a query and then loop it with coldfusion 12 89
Comparing 2 Lists Coldfusion 8 58
Passing value to a stored procedure 8 113
Apache error.log 19 74
This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

696 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