Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-07
6
Medium Priority
?
409 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:Jon Bredensteiner
  • 3
  • 2
6 Comments
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 100 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 400 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Jon Bredensteiner
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 400 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:Jon Bredensteiner
ID: 31579034
Thank you for the very detaild answer :)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

885 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