[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

cfquery or Stored Proc

Posted on 2014-03-17
2
Medium Priority
?
161 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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

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…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

650 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