9 Replies Latest reply on Jun 23, 2008 9:28 AM by Newsgroup_User

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

    em2g
      Hi,

      I have an application that has many users for many different companies logged in at the same time using the system. I use the application.cfm to call and set a bunch of variables that make the system work, such as preferences and things. Would it be better to use 5 or 6 queries all pulling one record, or call the queries only once at the start of the session and set 30 to 50 session variables based on the query results? Which method would bog the system down less? It seems that session variables use quite a bit of memory. I'm just trying to get the "simple answer" for this, if that's even possible.

      Please let me know your thoughts and experience on this.
      Thanks much,
      Jeff W!
        • 1. Re: Application.cfm - Should I use queries or set session variables?
          Level 7
          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
          • 2. Application.cfm - Should I use queries or set session variables?
            Kronin555 Level 1
            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?)
            • 3. Re: Application.cfm - Should I use queries or set session variables?
              em2g Level 1
              Hi Adam,

              I have 1 Application.DSN variable to set the datasource name for entire application, the rest of the session variables are company specific as well as user specific. The company specific variables set the global prefs for that company, and the user variables set the user specific prefs for each user under each company.

              I can fetch the data for some things on each page only when needed, however I find that most pages need to know something about the prefs to know what to show. That's probably why I put them in the application.cfm in the first place years ago.

              Thanks for your help,
              Jeff W!
              • 4. Re: Application.cfm - Should I use queries or set session variables?
                em2g Level 1
                Hi,

                Those 5 or 6 queries are quick, since they are only grabbing 1 row of data in 1 table. The number of users at peak is hard to say since we're just starting to add companies to the system, but in the near term there could be maybe 50 - 100 users accessing the system. I seriously doubt that they will all be doing something at the exact same second, but we have to plan for the possibility.

                We're running CF MX7, and I don't see how to view the amount of memory being used, either in the CF Administrator or in Plesk in our server. We currently have 1GB of RAM and the system usually seems to be using about half of that. There are about 30 session variables per company/user that get set upon login currently. How much actual memory gets taken up by them I cannot say, as I don't see how to check. If you know how to do it in CF7, please let me know.

                Thanks for your help,
                Jeff W!
                • 5. Re: Application.cfm - Should I use queries or set session variables?
                  Kronin555 Level 1
                  Seefusion supports CFMX 7 and you can run it for 2 hours in trial/developer mode without needing a license.
                  http://www.seefusion.com/
                  • 6. Re: Application.cfm - Should I use queries or set session variables?
                    BKBK Adobe Community Professional & MVP
                    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.

                    • 7. Re: Application.cfm - Should I use queries or set session variables?
                      Level 7
                      > the rest of the session variables are company specific

                      Can these possibly be in application[companyName]? Or can the application
                      scope be company-specific? This stuff doesn't sound USER-session-specific
                      anyhow.

                      > I can fetch the data for some things on each page only when needed, however I
                      > find that most pages need to know something about the prefs to know what to
                      > show. That's probably why I put them in the application.cfm in the first place
                      > years ago.

                      Unless something is used on *every* page hit (say: login credentials for a
                      secured area of the site), it should not be in Application.cfm, IMO.

                      If you need some stuff for every mouseclick for a specific section of the
                      site, then load that stuff up the first time the user hits that part of the
                      site, then evaluate whether it should be reloaded every mouseclick or
                      stored in session.

                      Perhaps also rein back your session timeout to be quite low, so that
                      memory is recycled shortly after it stops being needed.

                      --
                      Adam
                      • 8. Re: Application.cfm - Should I use queries or set session variables?
                        em2g Level 1
                        Thank you, all of you, for sharing your insight on this issue. You've helped me get a plan that makes sense for handling these things.

                        Thanks again,
                        Jeff W!
                        • 9. Re: Application.cfm - Should I use queries or set session variables?
                          Level 7
                          > Thank you, all of you, for sharing your insight on this issue. You've helped me get a plan that makes sense for handling these things.

                          So come on, spill: what's the plan?

                          --
                          Adam