• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Incrementing a variable in memory

New Here ,
Oct 27, 2011 Oct 27, 2011

Copy link to clipboard

Copied

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.

TOPICS
Advanced techniques

Views

1.5K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 28, 2011 Oct 28, 2011

Copy link to clipboard

Copied

An application variable might do the trick.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Oct 28, 2011 Oct 28, 2011

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 03, 2011 Nov 03, 2011

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Nov 08, 2011 Nov 08, 2011

Copy link to clipboard

Copied

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 -

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 08, 2011 Nov 08, 2011

Copy link to clipboard

Copied

<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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 08, 2011 Nov 08, 2011

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Nov 08, 2011 Nov 08, 2011

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 08, 2011 Nov 08, 2011

Copy link to clipboard

Copied

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Nov 08, 2011 Nov 08, 2011

Copy link to clipboard

Copied

LATEST

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[application.COMPANY_ID]#

    WHERE      COMPANY_ID = #application.COMPANY_ID#

    </CFQUERY>

</cflock>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation