9 Replies Latest reply on Nov 8, 2011 11:24 PM by BKBK

    Incrementing a variable in memory

    pjlefemine

      I have an older application that is querying a list, pulling out an AD from the list, then incrementing the database by +1 with every pull. The site has grown very popular and now the traffic is creating a ton of deadlocks. I tried using CFLOCK but that made things worse.

       

      Instead of writing each increment to the DB, I want to write it to memory and then run a routine which collects that value at some interval and update the DB.

       

      How would I do this? Would I use an Array or some other method? I am not all that familiar with CF.  Thanks in advance.

        • 1. Re: Incrementing a variable in memory
          Dan Bracuk Level 5

          An application variable might do the trick.

          • 2. Re: Incrementing a variable in memory
            insuractive Level 3

            Are you doing your incrementing in the same SQL statement as your select or are you performing them as 2 separate queries?  If you are doing 2 database interactions, its probably a good idea to combine them into a multi-statement SQL block or a stored procedure.  Something like this:

             

            <cfquery ...>

            Update myADList

            SET counter_field = counter_field + 1

            WHERE id_field = 2;

             

            SELECT *

            FROM myADList

            WHERE id_field = 2

            </cfquery>

             

            Or, alternatively, this seems like an excellent candidate for an asynchronous process.  Perhaps instead of having a counter field in your List table (which is giving you deadlock read/write problems), you created a new table that logged AD views.  You could capture a lot more details about your viewers (location/IP Address, datetime, etc) which would allow you to analyze your data a little more.  You could then fire the Log insert off as an asynchronous CF thread or an AJAX call.

             

            Hope that helps

            -Michael

            • 3. Re: Incrementing a variable in memory
              BKBK Adobe Community Professional & MVP

              pjlefemine wrote:

               

              I have an older application that is querying a list, pulling out an AD from the list, then incrementing the database by +1 with every pull. The site has grown very popular and now the traffic is creating a ton of deadlocks. I tried using CFLOCK but that made things worse.

               

              Instead of writing each increment to the DB, I want to write it to memory and then run a routine which collects that value at some interval and update the DB.

               

              How would I do this? Would I use an Array or some other method? I am not all that familiar with CF.  Thanks in advance.

              A result-set is more or less equivalent to a 2-dimensional array. So you may use an array or a struct to store the data. If the variable involves a user's increment, make it a session variable. Then collect the values within an interval in an application variable, as Dan says.

              • 4. Re: Incrementing a variable in memory
                PatLefemin

                I researched this using a global application variable and that is exactly what I want to do. My problem now is I have no idea how to do that and generate a unique global variable each time since the variable company_id is always random.

                 

                Here is my existing code.

                 

                **This section queries my database for current advertisers**

                 

                <CFQUERY NAME="GRABCOMPANYINFO" DATASOURCE="bg4" cachedwithin="#CreateTimeSpan(0,0,30,0)#">

                SELECT      COMPANY_ID, COMPANY, WEBADDRESS, graphic, flashad

                FROM         dbo.COMPANIES

                WHERE       (COMPANY_ID = #COMPANY_ID#)

                </CFQUERY>

                 

                **Once it pulls the ad the below code updates the views in the DB.

                 

                <cflock name="bannerkingwrite" timeout="10" throwontimeout="no" type="readonly">

                <CFQUERY NAME="UPDATEVIEWS" DATASOURCE="BG4">

                UPDATE     dbo.BANNERKING

                SET        VIEWS=VIEWS +1,MONTHVIEWS=MONTHVIEWS + 1

                WHERE       COMPANY_ID = #COMPANY_ID#

                </CFQUERY>

                </cflock>

                 

                 

                What I want to do is to replace the Update Query above with an application variable.  Any idea how I could do that? 

                 

                Thanks -

                • 5. Re: Incrementing a variable in memory
                  BKBK Adobe Community Professional & MVP

                  <cflock scope="application" timeout="10" throwontimeout="no" type="exclusive">

                   

                  <CFQUERY NAME="application.GRABCOMPANYINFO" DATASOURCE="bg4">

                      SELECT      COMPANY_ID, COMPANY, WEBADDRESS, graphic, flashad

                      FROM        dbo.COMPANIES

                      WHERE       COMPANY_ID = #application.COMPANY_ID#

                  </CFQUERY>

                   

                      <CFQUERY NAME="UPDATEVIEWS" DATASOURCE="BG4">

                      UPDATE     dbo.BANNERKING

                      SET        VIEWS=VIEWS +1,MONTHVIEWS=MONTHVIEWS + 1

                      WHERE      COMPANY_ID = #application.COMPANY_ID#

                      </CFQUERY>

                   

                  </cflock>

                  • 6. Re: Incrementing a variable in memory
                    pjlefemine Level 1

                    Thanks, but that is not what I was looking for.

                     

                    The first query is fine, I would like to remove the second query which updates the views with an application variable. So it would increment the variable in memory and then I can write it out to the database later.

                     

                    Something similar to this:

                     

                    <cflock scope = "Application" timeout = "30" type = "Exclusive">

                        <cfset application.views#company_id# = application.views#company_id# + 1>

                        </cflock>

                     

                    This does not seem to work.

                    • 7. Re: Incrementing a variable in memory
                      insuractive Level 3

                      You should be able to use CF's structure syntax to dynamically add/refer to named elements in a structure in order to store counts of your company IDs even if you don't know what they will be until run-time.

                       

                      <!--- Initialize application scoped structure in your application.cfc/cfm--->

                      <cfset application.stMyApplicationCounter = StructNew()>

                       

                      <!--- intialize/increment your counter after your query code --->

                      <cflock scope = "Application" timeout = "30" type = "Exclusive">

                           <cfif NOT isDefined("application.stMyApplicationCounter['#company_id#']")>

                                <cfset application.stMyApplicationCounter[company_id] = 0>

                           </cfif>

                          <cfset application.stMyApplicationCounter[company_id]++>

                      </cflock>

                       

                      That should give you a count for each company ID that has been accessed and you can use the keys in later queries if you like in order to update your counts asyncrhonously.

                      • 8. Re: Incrementing a variable in memory
                        BKBK Adobe Community Professional & MVP

                        I had been cooking up an example. It's along the same lines as Insuractive's.

                         

                        <!--- immediately after the variable application.COMPANY_ID is created, initialize the number of views for each company_id--->

                        <cfparam name="application.views['#application.COMPANY_ID#']" default="0">

                        <cfparam name="application.monthViews['#application.COMPANY_ID#']" default="0">

                         

                        <!--- later on ... --->

                        <cflock scope="application" timeout="10" throwontimeout="no" type="exclusive">

                         

                            <CFQUERY NAME="application.GRABCOMPANYINFO" DATASOURCE="bg4">

                                SELECT      COMPANY_ID, COMPANY, WEBADDRESS, graphic, flashad

                                FROM        dbo.COMPANIES

                                WHERE       COMPANY_ID = #application.COMPANY_ID#

                            </CFQUERY>

                         

                            <CFQUERY NAME="application.getViews" DATASOURCE="BG4">

                                SELECT     VIEWS, MONTHVIEWS

                                FROM     dbo.BANNERKING

                                WHERE   COMPANY_ID = #application.COMPANY_ID#

                            </CFQUERY>

                         

                            <!--- update the number of views for each particular company_id--->

                            <cfset application.views["#application.COMPANY_ID#"] = application.views["#application.COMPANY_ID#"] + 1>

                            <cfset application.monthViews["#application.COMPANY_ID#"] = application.monthViews["#application.COMPANY_ID#"] + 1>

                        </cflock>

                        • 9. Re: Incrementing a variable in memory
                          BKBK Adobe Community Professional & MVP

                          Referring to my last post, it might be more efficient to join the 2 queries into one. In fact I should have commented out the second query as optional. It now looks a bit confusing -- even to me! You would only run it where you needed to use the current number of views from the database.

                           

                          <cflock scope="application" timeout="10" throwontimeout="no" type="exclusive">

                           

                              <CFQUERY NAME="application.GRABCOMPANYINFO" DATASOURCE="bg4">

                                  SELECT      COMPANY_ID, COMPANY, WEBADDRESS, graphic, flashad

                                  FROM        dbo.COMPANIES

                                  WHERE       COMPANY_ID = #application.COMPANY_ID#

                              </CFQUERY>

                          <!---

                              <CFQUERY NAME="application.getViews" DATASOURCE="BG4">

                                  SELECT     VIEWS, MONTHVIEWS

                                  FROM     dbo.BANNERKING

                                  WHERE   COMPANY_ID = #application.COMPANY_ID#

                              </CFQUERY>

                          --->

                              <!--- update the number of views for each particular company_id--->

                              <cfset application.views["#application.COMPANY_ID#"] = application.views["#application.COMPANY_ID#"] + 1>

                              <cfset application.monthViews["#application.COMPANY_ID#"] = application.monthViews["#application.COMPANY_ID#"] + 1>

                          </cflock>

                           

                          <!--- much later in the code ... --->

                          <cflock scope="application" timeout="10" throwontimeout="no" type="exclusive">

                              <CFQUERY NAME="UPDATEVIEWS" DATASOURCE="BG4">

                              UPDATE     dbo.BANNERKING

                              SET        VIEWS=#application.views[application.COMPANY_ID]#,MONTHVIEWS=#application.monthViews[appl ication.COMPANY_ID]#

                              WHERE      COMPANY_ID = #application.COMPANY_ID#

                              </CFQUERY>

                           

                          </cflock>