11 Replies Latest reply on Apr 24, 2008 8:12 AM by CFMXPrGrmR

    Question on storing queries in session arrays

    CFMXPrGrmR Level 2
      I'm curious how much memory storing queries in session arrays take up. The query results themselves (about 5-6) are small, not more than 100 records. The site traffic is low, not more than 100k a month but is expected to jump to over 1 million. The web server (primary and failover) are beefy systems, I believe 4gigs of ram. So I don't imagine I should be concerned?
        • 1. Re: Question on storing queries in session arrays
          Level 7
          CFMXPrGrmR wrote:
          > I'm curious how much memory storing queries in session arrays take up. The
          > query results themselves (about 5-6) are small, not more than 100 records. The
          > site traffic is low, not more than 100k a month but is expected to jump to over
          > 1 million. The web server (primary and failover) are beefy systems, I believe
          > 4gigs of ram. So I don't imagine I should be concerned?
          >


          Ultimate a record set [i.e. query] is just an array of structures and as
          such should have only a small bit of overhead above and beyond the data
          in the query.

          Now what is in each query can very greatly. If that is five or six, one
          hundred record queries with each storing large CLOB or BLOB fields that
          could easily be many megabytes of data.

          But in most normal situations, no you should not have a problem. But
          only performance testing will say for sure.

          • 2. Re: Question on storing queries in session arrays
            CFMXPrGrmR Level 2
            Thanks Ian. No, they are simple text results that I want to carry throughout the whole site. Currently we're just querying on each page where we need the results. It's somewhat painful.
            • 3. Re: Question on storing queries in session arrays
              Level 7
              CFMXPrGrmR wrote:
              > Thanks Ian. No, they are simple text results that I want to carry throughout the whole site. Currently we're just querying on each page where we need the results. It's somewhat painful.

              You will also want to make sure these are unique results for each user.
              If the results are not unique per user, but are same for all users
              throughout the site, then you probably want to use application scope, or
              possible server scope. Then you only have one instance of the query
              used by all sessions rather then one million copies of the same query.

              • 4. Re: Question on storing queries in session arrays
                CFMXPrGrmR Level 2
                They aren't specific to the site users, just the site itself. These results are usually returned once each morning, so I would probably cache the query/array values. Are there any issues caching values in the application scope?
                • 5. Re: Question on storing queries in session arrays
                  Dan Bracuk Level 5
                  quote:

                  Originally posted by: CFMXPrGrmR
                  Thanks Ian. No, they are simple text results that I want to carry throughout the whole site. Currently we're just querying on each page where we need the results. It's somewhat painful.

                  There are two ways around this.

                  Method 1 - cache the query.
                  Method 2 - make the query a session varialbe.

                  This is outside my area of expertise, but my intuition tells me that method 1 will use less memory. That doesn't necessarily make it the best option. Among other things, you have to consider changes to the actual data.
                  • 6. Re: Question on storing queries in session arrays
                    CFMXPrGrmR Level 2
                    Right, I do want to cache it as well as make it accessible to the whole site. I was thinking the session variable but Ian mentioned that would give each user a copy of it, and that could mean "a million copies". If I can get it in the application scope and just have one copy, that's preferred (although I've never done this before).

                    If I use a CreateTimeSpan of 1 day in the query, then put those values in an application array variable should that work?
                    • 7. Re: Question on storing queries in session arrays
                      Level 7
                      CFMXPrGrmR wrote:
                      > Right, I do want to cache it as well as make it accessible to the whole site. I
                      > was thinking the session variable but Ian mentioned that would give each user a
                      > copy of it, and that could mean "a million copies". If I can get it in the
                      > application scope and just have one copy, that's preferred (although I've never
                      > done this before).

                      Just replace "session.myQuery" with "application.myQuery" and viola, one
                      copy of the query accessible by any template in the application
                      requested by any user.
                      • 8. Re: Question on storing queries in session arrays
                        Dan Bracuk Level 5
                        quote:

                        Originally posted by: CFMXPrGrmR

                        If I use a CreateTimeSpan of 1 day in the query, then put those values in an application array variable should that work?


                        I'm not sure. The potential problem is that the query never gets uncached. I don't know if the cache would expire one day after it was set, or one day after the most recent time it was requested from the cache.

                        If it's the latter, making it an application or server variable and refreshing it with a scheduled job seems like a reasonable way to go about it. In fact, doing it this way will result in less code throughout the various templates that use this data, they might not need a cfquery tag.
                        • 9. Re: Question on storing queries in session arrays
                          CFMXPrGrmR Level 2
                          I'll give these ideas a shot, thankfully I have a month or two to test it. I had thought about using a cfschedule to update the cached application query, I'll see how that goes.

                          Are there any benefits of making the application.queryname an array? Or is it just as well to access the query in the application scope?
                          • 10. Re: Question on storing queries in session arrays
                            Dan Bracuk Level 5
                            quote:

                            Originally posted by: CFMXPrGrmR
                            Are there any benefits of making the application.queryname an array? Or is it just as well to access the query in the application scope?


                            It already is an array.
                            • 11. Re: Question on storing queries in session arrays
                              CFMXPrGrmR Level 2
                              That's what I thought. I read a post you made on another thread about this being the case, so that'll save me a step. thnx