1 Reply Latest reply on Oct 8, 2007 9:17 AM by tjfrevert

    MultiStep Loop and insert - help!

    Level 7
      I have a list of properties that is very large on Sundays. Normally it works
      fine Monday-Saturday. The larger listing (54mg and 65,000 listings are
      causing a java.lang.OutOfMemoryError: GC overhead limit exceeded.

      What I would like to do is split the list in half and do it in 2 runs. I am
      just a little confused if this would work and if it would reduce the load if
      I create 2-3 functions and fire them in order.

      I use cfhttp to get the pipe delimited list and convert it into a query.

      Will this work? If not, what do I need to change?

      Function 1
      <cffunction name="getResidential1" access="private" returntype="void"
      output="false" hint="">
      <cfargument name="ResFile" type="string" required="yes">
      <cfhttp timeout="6600"
      url=" http://www.bpoprosonline.com/assets/property/#ResFile#" method="GET"
      name="Property" delimiter="|" textqualifier="" firstrowasheaders="yes" />
      <cfloop from="1" to="30000" index="i">
      <cfoutput query="Property">
      <cfquery name="loopProperty" datasource="bpopros">
      Insert Into Property (MLSNumber)
      VALUES = #MLSNumber#
      </cfquery>
      </cfoutput>
      </cfloop>
      </cffunction>

      Function 2
      <cffunction name="getResidential2" access="private" returntype="void"
      output="false" hint="">
      <cfargument name="ResFile" type="string" required="yes">
      <cfhttp timeout="6600"
      url=" http://www.bpoprosonline.com/assets/property/#ResFile#" method="GET"
      name="Property" delimiter="|" textqualifier="" firstrowasheaders="yes" />
      <cfloop from="30001" to="#Property.RecordCount#" index="i">
      <cfoutput query="Property">
      <cfquery name="loopProperty" datasource="bpopros">
      Insert Into Property (MLSNumber)
      VALUES = #MLSNumber#
      </cfquery>
      </cfoutput>
      </cfloop>
      </cffunction>


        • 1. Re: MultiStep Loop and insert - help!
          tjfrevert
          Personally I would try to find a way to do a sql mass import via a txt file.

          If that's not an option for whatever reason. I would try to get it all in one query statement.

          <cfhttp timeout="6600" url=" http://www.bpoprosonline.com/assets/property/#ResFile#" method="GET" name="Property" delimiter="|" textqualifier="" firstrowasheaders="yes" />

          <cfquery name="loopProperty" datasource="bpopros">

          <cfloop list="#Property#" delimiters="|" index="i">
          INSERT INTO Property (MLSNumber)
          VALUES = (<cfqueryparam cfsqltype="cf_sql_varchar" value="#i#">);
          </cfloop>

          </cfquery>

          If you are using mySQL you can get it all into one insert statement:

          INSERT INTO Property (MLSNumber)
          VALUES = (1),(2),(3),(4);

          Keep in mind that if you split it into two functions and run them at the same time it will most likely give you the same error.