Solved

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

Posted on 2009-05-07
6
367 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
  • 3
  • 2
6 Comments
 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

947 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now