Solved

cfquery or Stored Proc

Posted on 2014-03-17
2
159 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

690 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