Caching query question

[paulm1677332]

Hi,

I've never used cachedwithin parameter before in queries as the queries are generally dynamic but i'm potentially thinking it might be of benefit to us. The question I have is with a lot of users and active sessions, would adding this cachedwithin parameter to a few queries overload the cache or have negative affects to performance. I don't know enough about how the cache works or what kind of space it has to operate. Is there a benefit of using that parameter if the queries are dynamic. All I can see in the documentation is that for dynamic queries it will create a new one for each but it doesn't warn against using it if that is the case.

Thanks

[Dave Watts]

Generally, no, CF will almost certainly have plenty of available resources for caching. The only question you have to ask is whether the cache will be used or not. If not, there's no point in doing it. Most queries aren't dynamic enough to avoid any duplication. If they are, there's no benefit from caching.

Dave Watts, Eidolon LLC 

[paulm1677332]

Hi Dave,

We have a number of queries to return various counts on the homepage so for anyone using the system and constantly returning to the homepage these queries would constantly be reran. The problem is for many of the users these queries would vary depending on certain criteria so there would be lots of similiar queries cached I would say. If the cache was able to handle it I think there should be value adding cachedwithin to a few queries.

Thanks

[Charlie Arehart]

Do note that there's a cf admin setting (on the server settings>caching page) that sets the 

max number of cached queries that would be kept. Beyond that, the least recently used would be removed from the cache. The default is 100, and in your situation Paul it would seem wise to raise that.

That's the number of distinct sql statements including where clause values , so yes even a single cfquery could fill that if called 100 times with different values. "Not that there's anything wrong with that." 🙂 But you'd almost certainly want to raise that limit.

And it's a limit per application, and can even be configured to different values per application (in application.cfc or cf).

Sadly, there's no means in the admin or code to know the SIZE of the cache or its elements. Indeed, a single cache entry could hold one record or a million. It's still one cache entry. 

HTH. 

[paulm1677332]

Thanks Charlie,

Interesting to know its per application. Thats great to know. I think i can definately up that value and look at a few queries to start using it now.

[BKBK]

Hi @paulm1677332 ,

Negative affects on performance? How so? When used appropriately, query-caching is actually one of the main ways to increase performance in ColdFusion applications. ColdFusion reuses the data without having to get it from the database.

How many users are you talking about in your situation? I once worked on a website that had thousands of users per ColdFusion instance per day. A number of queries held user-data and session-data that remained unchanged throughout the working day. Those queries were cached. Yes, per user, per working day, for thousands of users. 

There were three main factors we took into consideration when caching queries:

1.  Constancy of the query data "within" a given period of time. For example, user or session data that remained unchanged throughout the working day;
2.   The amount of RAM available to the ColdFusion instance. For example, on an instance running on 16 GB RAM, queries could be cached for several thousand concurrent users every working day;
3.   Fusionreactor (Ultimate edition) was used to constantly monitor memory use.

[paulm1677332]

Thanks BKBK,

What value had you set the for Maximum number of cached queries to be, out of curiousity?

[BKBK]

6000, if I remember correctly.

That said, I would again strongly recommend using a tool, such as Fusionreactor, to monitor memory use. 

The amount of memory used will of course depend on the size of the cached queries. If too much memory is used, then you will have to (1) reduce the setting for maximum number of cached queries, or (2) (if the cached queries are an indispensable requirement) increase the amount of RAM available to the ColdFusion instance.