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?

451lsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PluckaConnect With a Mentor Commented:
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
 
LeaperJPDConnect With a Mentor Commented:
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
 
mrichmonConnect With a Mentor Commented:
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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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
 
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
 
Tacobell777Connect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.