when do queries actuualy execute/best practice for creating them

[bebblebrox]

Hi,

when using the cfquery tag, when does the query actually execute? does the mere presence of the tag execute the query and store the results for later use, or is the query executed when you take some action on it, such as a cfloop?

assuming that the query is not executed until you take an action on it, would it be a good idea to have all your queries in one file and simply include that file for pages that need data access?

thanks,

 

[ecobb]

A query executes when the page is processed. If you have debugging turned on, you can write a simple page that has nothing but a query in it (no output or looping), and still where the query executed, the time it took, number of results, etc...

If you have all of your queries in one file and include that file when you need a query, then EVERY query on that page will be executed EVERY time that file is referenced.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[webmigit]

One option for you if you really want to centralize your queries to create a file called qfetch.cfm and make the contents:

<cfparam name="grab" default="">
<cfif grab is "">
  You did not select a query by the
  grab parameter. Processing of page
  is stopped.
  <cfabort>
[red]<cfelseif grab is "UserList">
  <cfquery datasource="ds_name" name="userlist">
    select * from users
     order by username
  </cfquery>
<cfelseif grab is "NewsList">
  <cfquery datasource="ds_name" name="newslist">
    select * from news
     order by username
  </cfquery>
<cfelseif grab is "UserProfile">
  ...and so on and so forth...[/red]
</cfif>
<cfset grab="">

And then you would call the page by:

<cfset grab="...">
<cfinclude template="qfetch.cfm">

Though I wouldn't really suggest this method, its best to put code in the page that concerns it, or by reference.. This seems like a lot of overkill.. especially with as lengthy as the file could get..

Only the select query would run, not all the queries. So that serves that purpose.

You would change the red text... you can have as many cfelseifs as you like... As to particular order.. You could order by most popular first.. As once cfif finds a match, it processes the action and then skips the rest.

Also, to others who might reply... I know this could be a custom tag and the syntax would appear nicer, but then you deal with a difference in scopes. (caller scope mostly).

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.

 

[imstillatwork]

overkill, its just another step. having all you queries in one page can only pe usefull if you forget what templates have what queries. but you should now that, or be able to find out very easy.

 

[TruthInSatire]

I've done the Central query template in an effort to "tidy" up the application.

Nightmare! don't do it and I'll tell you why.

My template is about 1500 lines.
If you're not a big fan of extensave commenting... You will be soon.
Most of the template are queries. Update, insert and select.

Several pages use the template to do the majority of the processing. I've made the application so users could do an action several different ways. Each accomplishes the same thing however it has to take place in a seperate area of the query template (formHandler.cfm).

so now I have redundant queries on the page that are only called if needed. No big deal right? It doesn't use any processing if it is skipped over. Wrong forget to update that query in EVERY <cfif> and you've got problems.

My customers call me up and say "Hey the data is wrong" I ask "How did you change the data" (remember there are some diferent ways to do the same thing to make it user friendly). They respond with the typical, "I don't remember"

I'm NEVER using a form handler again, you can't make me.


Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[webmigit]

Though I wouldn't really suggest this method, its best to put code in the page that concerns it, or by reference.. This seems like a lot of overkill.. especially with as lengthy as the file could get..

I feel the same way.. I was just giving the answer to the question asked.. But no, I don't like that method.. I think I _started_ to use it once, saw the nightmare it was going to be, and left it behind.

Identical queries are going to be basic:

Select * from table

Because its pretty rare that a whole bunch of queries will will be really advanced and identical.. Advanced queries serve a pretty defined purpose.

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.