Solved

ColdFusion Server Side Query vs. SQL Server View - Which is Faster \ Best Practice

Posted on 2009-05-07
6
386 Views
Last Modified: 2013-12-24
I've been asked to help with a ColdFusion website, and I'm still a novice, but there is one thing the main developer does that I find odd (well, there are many things he does that I don't agree with, but that's beside the point).  Anyways, he does not have one view in his SQL Server db; he recreates the queries in every page that pulls data, and that includes 90%+ of the pages.

I know it makes since to create queries in some of the pages, especially when they are only going to be used once, but I would think it would be better to create the majority of the queries in the SQL Server, and then simply call them in.

What is the best practice to follow?  Which is faster, having the ColdFusion server do the work, or allowing the SQL Server to do the work?

Thanks, Jon
0
Comment
Question by:JBredensteiner
[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
  • 3
  • 2
6 Comments
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 25 total points
ID: 24326870
obviously: "SQL Server to do the work " faster
However, it depends on your application logic, etc

run sql profiler - to see and compare...
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 100 total points
ID: 24327094
> Which is faster, having the ColdFusion server do the work, or allowing the SQL Server to do the work?

Really, SQL Server _always_ does the work.   When you execute a regular cfquery, CF evaluates any variables in the statement and then submits the sql string to SQL Server for processing.  CF little to do with the actual query plan.  That is all done by SQL Server, whether you are running a view, select statement or stored procedure.   So the main difference is in your database, not CF.  

Having said that, using cfqueryparam (ie bind variables) can promote ms sql's reuse of query plans.  That helps improve performance.  So, you should definitely use cfqueryparm with all queries.  
0
 
LVL 52

Expert Comment

by:_agx_
ID: 24327108
>  CF little to do with the actual query plan.

    CF _has_ little to do with the actual query plan. That is all done by SQL Server ...
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:JBredensteiner
ID: 24327496
So it sounds like it really comes down to how many times a particular query is going to be used.  If it will only be used once, then it might as well reside in the CF page, rather than clutter up the database.

Thank you both for your help, Jon
0
 
LVL 52

Assisted Solution

by:_agx_
_agx_ earned 100 total points
ID: 24327661
Yes, to a degree it is a matter of personal preference.  I have seen some applications that use stored procedures _only_  and others than used queries and views only.  The advantage of using stored procedures is that you have all of the code in the database.  But there are also some things you do more easily with CF query.  So, again it is a matter of personal preference.

Whichever method you choose, I would suggest you try and abstract the database logic (ie not have queries and display code all residing in the same page).  Put database logic in cfc's.  Otherwise, you end up with spaghetti code.

0
 

Author Closing Comment

by:JBredensteiner
ID: 31579034
Thank you for the very detaild answer :)
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

734 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