CFAPPLICATION - include queries

[hpvic03]

How do i put a cfquery in my application file so that the query runs on every page in that directory?

 

[TamedTech]

In exactly the same way you would include a query on any other page.

Rob

 

[hpvic03]

you mean just put the query below the <cfapplication> tag?

 

[NorthStarDA]

does the query really need to 'run' on every page? or could you cache a recordset in the application scope that can always be referenced? (could the data change between page requests, and do you need to account for that)

for example, if you have a query that returns settings for the application, but the settings rarely change - it would not make much sense to query for those on every page request.

=========================================
Don't sweat the petty things and don't pet the sweaty things.

 

[GUJUm0deL]

hpvic03, what kind of a query is this?

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[hpvic03]

its just a select query that selects data from a table that i use in almost every page. i guess thats caching a recordset. how do i do this?

 

[GUJUm0deL]

Caching a recordset means you query the database once and then use the same resultset (usually done for 24 hr span).

A cached query is ONLY useful if the recordset's data is not changed often. In other words, if the query returns different recordset every ten minutes then a cached query is not helpful.

http://livedocs.macromedia.com/cold...xt=ColdFusion_Documentation&file=00000316.htm

for more info on cached queries.



____________________________________
Just Imagine.

http://www.imajinarts.com

 

[imstillatwork]

when you load things in the application scope, make sure you do a little switch, so that you don't reload it every request.

<cfif NOT structKeyExists(application,"isReady") OR sructKeyExists(url,"reset")>

<cfquery datasource="" name="application.yourquery">
select whatever
</query>

<cfset application.isReady = 1>

</cfif>

If you don't make a switch like this, you're loading your application scope variables every request, which completly defies the point of them I guess.

alternativly, just cache the query outside of the application scope

<cfquery datasource="" name="yourquery" cachedwithin="#createTimeSpan(0,1,0,0)#">
select whatever
</query>

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[NorthStarDA]

well it's not really 'cache' but basically you can store the query itself or a structure in the application scope so that it can be referenced on each page - for example in your application file (or a file included from application) you might have something like this:

<cfparam name="loadMyQuery" default="false">
<cflock scope="application" type="readonly" timeout="10">
	<cfif not isDefined('application.myQuery')>
		<cfset loadMyQuery = true>
	</cfif>
</cflock>

<cfif loadMyQuery>
	<cfquery datasource="yourDSN" name="getData">
	SELECT ... FROM ... WHERE ...
	</cfquery>
	<cflock scope="application" type="exclusive" timeout="30">		
		<cfset application.myQuery = getData>
	</cflock>
</cfif>

me personally, i would store a structure instead of a query- let's say that this query brings back site settings specific to the application, if someone changes the settings in the front end, you can have that code also update the structure in the application scope, and when other users request another page, they get the newest settings without having to pull from the db.

=========================================
Don't sweat the petty things and don't pet the sweaty things.

 

[imstillatwork]

A cached query is ONLY useful if the recordset's data is not changed often. In other words, if the query returns different recordset every ten minutes then a cached query is not helpful."

On a very busy site, caching to the hour or even shorter can save huge ammounts of db server time, while keeping data pretty fresh.

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[Wullie]

On a very busy site, caching to the hour or even shorter can save huge ammounts of db server time, while keeping data pretty fresh.

I'd take that further and say ANY caching on a busy site would help dramatically. Let's say you have for example 10 people on your site every minute and they all visit 5 pages each, thats 50 times the query has to run per minute, or just less than once per second. If you cached that for even one minute, you would drop that 50 down to 1. Now that's only for one query, if you had 10 queries per page that's 500 queries per minute (or just over 8 per second) and caching would bring it down to 10 per minute.

For content that doesn't change often, there is absolutely no reason not to cache it, unless you only have a very tiny amount of visitors to your site.

Hope this helps

Wullie

YetiHost - Quality Coldfusion 7/Windows Hosting

The pessimist complains about the wind. The optimist expects it to change. The leader adjusts the sails. - John Maxwell

 

[GUJUm0deL]

On a very busy site, caching to the hour or even shorter can save huge ammounts of db server time, while keeping data pretty fresh."

True but only hpvic03 knows how often the query set is updated.

But on traffic heavey site, if the query cache runs every 15 min, you'd have to notify the users that there is a 15 minute delay.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[hpvic03]

thanks alot guys... hopefully the site is going to be busy, so i think i'll cache it

 

[imstillatwork]

good idea, you can always play with the cache times and reseting the cache immediatly after an update or insert, etc.

as the site grows, you may need to tweak things in other places that you would not have though.

use query attribute result and getTickCount to find weak spots.

Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!