Caching query question

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.

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. 

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