[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Best Practice, CFQUERY on page vs CFINCLUDE

Is it a better practice to just add the query within the page or to use a CFINCLUDE and bring that query to the page.

For example,
mypage.cfm
<cfquery name="myquery">
blah blah blah
</cfquery>
----- or ----
mypage.cfm
<cfinclude template="getmyquery.cfm">
0
Lee R Liddick Jr
Asked:
Lee R Liddick Jr
  • 6
  • 6
  • 5
4 Solutions
 
gdemariaCommented:
I lot of people may tell you it's best to create shared code and include it on multiple pages, this is because if you have to make changes to your query, you can do it in only one place.

I think the answer is "it depends" .. you want your query to be specific to what needs to be fetched... you don't want to SELECT * but instead list the columns... if you only need a few columns on one page, then you don't want to select more columns or select * just to make the query share-able.   It's the same situation with the where clause, you want to fetch what you need, no more.

In my websites, if I create a specific query that is significant (lots of joins and such) and this query needs to be called from multiple pages, then I create a cfinclude and share it.  Otherwise, I do not.  Most of the time, I out the query right in the page so I can make it very specific to that page.

0
 
gdemariaCommented:
oops..

  Most of the time, I put the query right in the page..
0
 
_agx_Commented:
(no points...)

> I think the answer is "it depends" ..

I agree.  But my preference is to use cfincludes for presentation stuff (html), and put shared queries in cfc's.  In a lot of ways they're more flexible.  Same "sharing" concept though.  
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Lee R Liddick JrReporting AnalystAuthor Commented:
I also was told that it is better to put your queries in a PROC?  Any pros/cons to doing it this way rather than writing the query on the page or in CFINCLUDES?
0
 
gdemariaCommented:
I think putting a query into a database procedure is really over-kill, a huge pain and doesn't return any significant benefits...

I query executed via a cfquery would take a few milliseconds to run, do you need it faster than that?  You can run 50 queries on a page without noticing any performance hit.   Now you want to update your query... you have 75 queries in the database, you have to determine which query goes with which page.. and then alter the procedure to recreate it... and what if you just want to look at it.. again, you have to dig through the database to find the correct procedure to view the query it contains.  But hey, maybe you could have a convention when your queries are named after your pages, such as  "productList-query-to-get-products"   and "productList-query-to-get-categories"  etc...

I think I may be against it..   hmmm.  :)

(the only exception may be a really large complex query - when having performance issues)

0
 
_agx_Commented:
> I also was told that it is better to put your queries in a PROC?

That one's up for debate IMO. I've worked on projects that ONLY used stored procedures and one's refused to use any.  There are pros/cons to both.  

Stored procs are a great tool.  They're designed to bundle complex sql that you really shouldn't run in a cfquery.  It's also nice to have the logic centralized, so you can make most changes at a db level without affecting CF.  But by the same token, IMHO you can often generate dynamic sql statements more easily (and safely) in the CF layer.  I've seen a lot of MS stored procs that resort to dynamic sql which can be very dangerous if the data isn't scrubbed.

So I think it's another "it depends" situation.  Use them if they're the right tool for the job, not just "because".
0
 
_agx_Commented:
> I think putting a query into a database procedure is really over-kill, a huge pain
> and doesn't return any significant benefits...

Personally I love them.  But I don't think we can make a blanket statement without knowing what kind of "query" and what the goal is :)  Normally there isn't a reason to wrap single SELECT's in a proc (IMO). But there are exceptions. I've seen it used as a way to centralize db access when the db was shared across multiple apps.  Also it can be used as as security mechanism.  But I'd agree you shouldn't use them just because people say you should.  It all depends.
0
 
gdemariaCommented:
Agreed, I was thinking of "most" queries when I made my statement... I did include one exception (big/complex queries), security is another reasonable exception

0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Okay...makes sense as the only query I have a PROC for is very complex and gathers a lot of data from various tables.  My original best practice question was geared towards my normal every day queries.  I appreciate all of the comments you both have provided.  
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Can always count on you two...thanks.
0
 
gdemariaCommented:
leer, I think you accidently accepted both of my comments instead of one of mine and one of agx's to split the points.   It's cool with me if you want to "request attention" to re-open question and split them..

0
 
_agx_Commented:
I'm cool either way as leerljr68's questions are always interesting :)
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Interesting huh?  Hahahahaha...just trying to learn this stuff on my own as I go.  You guys have been a god send to me.  I'm actually attending EE U and you two are my favorite professors!  LOL
0
 
gdemariaCommented:
that's a nice compliment, thanks
0
 
_agx_Commented:
lol. Not "interesting" in a bad way.  It's fun when people ask intelligent questions about how things work or how to approach things.  It usually opens up a lively discussion between fellow geeks which I enjoy, and in the process I sometimes learn stuff new too. So it's win-win all around :)
0
 
Lee R Liddick JrReporting AnalystAuthor Commented:
Good stuff!
0
 
_agx_Commented:
Thanks guys! Until next time :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now