Solved

When does <cfquery> execute

Posted on 2004-04-21
9
520 Views
Last Modified: 2013-12-24
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
Comment
Question by:451ls
  • 2
  • 2
  • 2
  • +2
9 Comments
 
LVL 2

Assisted Solution

by:LeaperJPD
LeaperJPD earned 40 total points
ID: 10882457
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
 
LVL 35

Assisted Solution

by:mrichmon
mrichmon earned 40 total points
ID: 10882972
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
 

Author Comment

by:451ls
ID: 10883009
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
 
LVL 35

Expert Comment

by:mrichmon
ID: 10883157
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:Plucka
ID: 10883927
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
 
LVL 17

Expert Comment

by:Tacobell777
ID: 10884002
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
 
LVL 17

Assisted Solution

by:Tacobell777
Tacobell777 earned 20 total points
ID: 10884012
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
 

Author Comment

by:451ls
ID: 10884348
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
 
LVL 18

Accepted Solution

by:
Plucka earned 100 total points
ID: 10884378
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now