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

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
Jon BredensteinerProject ManagerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
_agx_Connect With a Mentor Commented:
> 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
 
Eugene ZConnect With a Mentor Commented:
obviously: "SQL Server to do the work " faster
However, it depends on your application logic, etc

run sql profiler - to see and compare...
0
 
_agx_Commented:
>  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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
Jon BredensteinerProject ManagerAuthor Commented:
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
 
_agx_Connect With a Mentor Commented:
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
 
Jon BredensteinerProject ManagerAuthor Commented:
Thank you for the very detaild answer :)
0
All Courses

From novice to tech pro — start learning today.