Go Premium for a chance to win a PS4. Enter to Win

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

When does <cfquery> execute

Does a <cfquery> execute when it is encountered on the page or only
when a <cfoutput> for that query is encountered or both?

Is it a way of optimizing performance to put queries in conditional statements
rather than at the top of the page where it will always be executed?

0
451ls
Asked:
451ls
  • 2
  • 2
  • 2
  • +2
4 Solutions
 
LeaperJPDCommented:
It executes as soon as CF sees it on the template.  Yes, you can put CFQUERY in a conditional statement to only execute if you need it.  That is why you do not put CFQUERY in your application.cfm unless you really really need to.  Even then, you can set the query as an application variable, and conditionally run the query only if the application variable is not a query.  So it sits in memory and can be used without a hit to the database.  This will pay off later when your site gets busy, or right now if your site is already busy.

Use isquery(somequeryname) to determine if a query exists.

cheers,

Jason
0
 
mrichmonCommented:
The call to the database is executed when a cfquery is encountered in the page.  If you put it in an if then it only runs if that section of the cfif executes.

Yes it optomizes performance to have the queries in conditional statements so that they are only executed as needed.

Now as for the cfoutputs.  Once a select query is run is sits in memory of the page.  Each time it hits a cfoutput or cfloop based on that query the records are looked at FROM THE QUERY RESULTS IN MEMORY.  This means that the actual database may be different.  This is rare, but on a heavily hit site it could happen.  But if you refreshed the page you would then have the query re-run and the newest results at the time the query was placed into page memepry would again be displayed.

Does this help or confuse more?
0
 
451lsAuthor Commented:
This sounds like the perfect answer, if I understand. Can you clarify if you are saying it IS OK to put it in the app.cfm if it is defined as a variable - then it is only executed when you output the variable?

0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
mrichmonCommented:
No it is NOT "only executed when you output the variable"

It is executed when the query is encountered and stored in page memory as the rest of the page code is traversed.  It is only output from that variable in page memory when it encounters the cfoutput or cfloop.

So as leaperJPD said it is a BAD thing to put a query in the Application.cfm file as that means it gets executed every time a page is called.

If you need to have it called on a page then run it at the top of the page before even the HTML tag.  If you need it to run only in certain cases - then use a cfswitch or cfif to control when the query is executed.

The slow part of query execution is the call to the database - not looping over the result set.
0
 
PluckaCommented:
Hi 451ls,

> Does a <cfquery> execute when it is encountered on the page or only
> when a <cfoutput> for that query is encountered or both?

A <cfquery is executed when it is encountered, the <cfoutput is not related, you do not need a <cfoutput

> Is it a way of optimizing performance to put queries in conditional statements
> rather than at the top of the page where it will always be executed?

Yes you can just put the query in any condition ie:

<cfif Sun eq "Yellow">
    <cfquery>
        select * from x
    </cfquery>
</cfquery>

If you want to only execute the query once. you can do something like.

<cfif NOT IsDefined("Session.myQuery")>
    <cfquery name="Session.myQuery")>
        select * from x
    </cfquery>
</cfif>

If you put this in your application.cfm this would execute once when the app is first run and then be available for all time to use.

NOTE: I will contain the results of when it is executed, and will not have new values when next output or referenced. So this is only usefull if the results of the query don't change.

Regards
Plucka
0
 
Tacobell777Commented:
Yes you can add a query in a conditional statement ( I thought I just repeat what everyone else has ;-))

If you want to optimize performance of a query you need to cache it, example;

<cfquery name="myQuery" datasource="myDataSource" cachedwithin="#createTimeSpan(0,4,0,0)#">
SELECT *
FROM yourtable
</cfquery>

Where the integer 4 is the number of hours you want to cache it.

This is something you use for queries that do not change often, for example queries for dropdowns etc.

This query can be anywhere in your page and does not have to be in a conditional statement, it will not perform the q
0
 
Tacobell777Commented:
Yes you can add a query in a conditional statement ( I thought I just repeat what everyone else has ;-))

If you want to optimize performance of a query you need to cache it, example;

<cfquery name="myQuery" datasource="myDataSource" cachedwithin="#createTimeSpan(0,4,0,0)#">
SELECT *
FROM yourtable
</cfquery>

Where the integer 4 is the number of hours you want to cache it.

This is something you use for queries that do not change often, for example queries for dropdowns etc.

This query can be anywhere in your page and does not have to be in a conditional statement, it will not perform the query again unless the where clause changes, if you have one.
0
 
451lsAuthor Commented:
I'm increasing the points because I got good ideas that I needed from several of you.
I'' assign them after this final summation. If anyone cares to confirm or deny or add to, much appreciated.

1. cost of query execution is when query is encountered on page and each time page is reloaded (by you or anyone)

2. therefore conditionals obviously help reduce cost if query isn't always used on page

3. all cfoutputs will use memory version of data from queries from SAME page (data could be different from database)

4. use of queries on app.cfm not generally good idea, BUT IS A GOOD IDEA if data is to be used on many pages and it is embedded in a NOT isDefined conditional.

5. if results on a more dynamic query than a pulldown can be reused by other user's downloading the same page, cache it for the window they can tolerate static results. These results will be shared by all sessions executing that page for cache window.

6. Here's the part I am not 100% on: If say 4 pages use a query that is the same and you want it to be executed only once for a GIVEN user session, is there a way to have these several pages share these results for the life of the session only? Extrapolating from PluckA's response, I think the answer is to have all 4 pages create the query as a session.query and only re-execute it if it doesn't exist. There is no need to cache this because the results will be there until the session ends, right?

0
 
PluckaCommented:
451ls,

> 1. cost of query execution is when query is encountered on page and
> each time page is reloaded (by you or anyone)

Correct!

> 2. therefore conditionals obviously help reduce cost if query isn't
> always used on page

Correct!

> 3. all cfoutputs will use memory version of data from queries from SAME
> page (data could be different from database)

Correct!

> 4. use of queries on app.cfm not generally good idea, BUT IS A GOOD
> IDEA if data is to be used on many pages and it is embedded in a NOT
> isDefined conditional.

Correct! If data isn't changing.

> 5. if results on a more dynamic query than a pulldown can be reused by
> other user's downloading the same page, cache it for the window they
> can tolerate static results. These results will be shared by all sessions
> executing that page for cache window.

Correct! You can cache the page or the query to minimize load.

> 6. Here's the part I am not 100% on: If say 4 pages use a query that
> is the same and you want it to be executed only once for a GIVEN user
> session, is there a way to have these several pages share these results
> for the life of the session only? Extrapolating from PluckA's response,
> I think the answer is to have all 4 pages create the query as a
> session.query and only re-execute it if it doesn't exist. There is no
> need to cache this because the results will be there until the session
> ends, right?

Correct, You only need the query once, say in application.cfm which will be stored in the session.myQuery variable and be accessible to every page. No page needs to have the <cfquery statement other than the application.cfm in this case. Or they all have the query but only execute it if it is not already in session.myQuery as per my example.

Regards
Plucka
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now