Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2009-05-07
6
Medium Priority
?
402 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
[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 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

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…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

704 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