I am putting together a simple banner rotator system for my coldfusion application and while I have a pretty clear idea about how to achieve it, I am more concerned about the actual performance of the solution.
All of the solutions I have seen on internet rely on continual interraction with the underlying database (consisting of pulling out recods' ids from database - schuffling them - displaying the corresponding banner - incrementing hits/click counters and updating back to the database).
Considering my site has one hundred visitors per hour, each one visiting 10 pages on average, this means that there are 1000 read and 1000 update database transactions each hour only to display the ad banners, and even though the read queries can be easily cached the updates cannot.
My question is whether copying the query containing all banners to a new object in the APPLICATION scope would be a reasonable aproach? It seems not to be overly obvious considering you need to (occasionally) synchronize the database as well due to lack of application scope persistence between possible server restarts.
It's good that you're trying to think outside the box. Sometimes what is easiest is not what is best.
Like Matt Busche said, I don't think the weight of 2k queries per hour (2 every 3.6~ seconds) is a large concern but I wanted to focus on something you said in a comment.
Basically, the banner should update only the number of impressions left and/or hit/click counters. An obvious shortcoming of the application scope approach is that the application would have to be restarted every time you add a new banner to the database.
There are a number of ways to avoid doing this in CF. I think the most practical (if you're not querying the database directly), is to use cfcache.
It is not unreasonable to inform the clients that the banners will be added "in the next X hours" and use any of the caching methods CF offers to cache the content.
<cfcache><cfset cache_fresh = false>
<cfcache timespan="#createTimeSpan(0,12,0,0)#" ...> <!--- Timeout set to 12 hours --->
<cfset cache_fresh = true>
<cfif StructKeyExists(Application,"BannerObj")>
object exists, update table.
</cfif>
Query table
Update Application.BannerObj with new data.
</cfcache>
#cache_fresh# is a variable you can use to see if the cache was just created in this request. That information might be useful to you.
The nice part about a cfcache solution is that you can force an update by flushing the cache if you did want to update the system each time a banner was added or had a situation where a banner had to be removed now, for instance if it violated your ToS.
<cflock name="upbanners" timeout="10">
<cfif Application.ReGetData is true>
Query for data and copy to application scope.
<cfset Application.ReGetData = false>
</cfif>
</cfif>
If the banner system isn't updated for several days, the code is never rerun. All you need to update immediately is set ReGetData to true, the next request to the banner system will refresh the object.