Application.cfm - Should I use queries or set session variables?

1) One query is, in general, preferable to six. Each query,even a small one, implies a connection to an external machine, which introduces risks and an overhead.

2) Use Application.cfc instead. It enables you to have more fine-grained control of the application. For example, if you want a piece of code to run just once at the beginning of the user session, you will put it in onSessionStart.

3) Store a query in session scope, if necessary. You do so by using a value like session.myQuery for the name attribute in the cfquery tag. That effectively caches the query during the user session.

In order to give you a simple "do it this way" answer, more information is needed.

How long does it take to run those 5-6 queries on each request? How many users are logged in at peak times? How much data would you be storing in the session for each user? How much RAM do you have on your server?

On one application, we've decided to go the session variable route. The size for each users' session maxes out at 2.5 KB. We have 512 MB dedicated to CF, but can easily scale up on the hardware we have to 1.5 GB or more. At 512 MB, 2.5 KB per user session, we can handle a little over 200,000 concurrent users (assuming all CF memory could be utilized for session variables, which isn't true). There are only 6,000 users total in our system, so we have quite a bit of headroom.

Are you on CF8? If so, open up the server monitor, start monitoring, profiling, and memory tracking, and log in with a couple users (with your code setup to store all that info in the session). See how much storage each session takes. Then look at your server. How much RAM do you have? what do you currently have allocated to ColdFusion? What's your peak concurrent user level? Do you have headroom? Also, see if the benefits of storing it in the session are even worth it (how long do those 5-6 queries add to each requst?)

Is the data that you're fetching user-specific or application-specific?
It's not entirely clear from your post. It would help a lot answering your
question if you were more specific.

I would be inclined to minimise the amount of DB interaction going on in
Application.cfm, as it's being re-run every mouse-click; I don't imagine
the underlying data is being changed as often as that, so it makes little
sense to keep fetching it afresh.

Does every request require ALL of this data? Or can you fetch it "just in
time" before you need it?

If you go the query route, give some thought to caching them, rather than
hitting the DB automatically ever time. Let CF handle the memory
management for this.

--
Adam