0

I am using the following code, the code works but the issue is for 20000 records, the loop is taking too much time and timing out.

so basically it is like this

<cfset x = new myapi()>
<cfparam name='length' default="0">
<cfparam name='start' default="5000">
<cfset iQEmpty = false>
<cfloop condition="true">
    <cfquery name="rs">
    select * from mytable limit #start#,#length#
    </cfquery>
    <cfset start += rs.recordCount>
    <cfset myst = queryToJson(rs)>
    <cfset call = x.UpsertData(myst)>
    <cfif rs.recordCount NEQ 1000>
        <cfbreak>
    </cfif>
</cfloop>

Can't use coldfusion latest because i am still stuck on cf11 and upsert is expecting a json data to be sent

rrk
  • 15,677
  • 4
  • 29
  • 45
Jianxee
  • 15
  • 4
  • Have you tried saving `queryToJson(rs)` into and array and call `` in a separate loop of that array? – rrk Jan 13 '22 at 19:15
  • 1
    What does `UpsertData()` actually do? Does it save the data into another table? – Sebastian Zartner Jan 13 '22 at 19:51
  • UpSertData is call to the api where it pushes the data to the api for uploading and inserting @rrk Can you explain what you mean by putting it separate, how that can help?, its already doing a batch upload – Jianxee Jan 14 '22 at 13:21
  • 1
    Can this be done using databases only? If we are talking more than one database, there are ways to get them to talk to each other. – Dan Bracuk Jan 14 '22 at 17:19

2 Answers2

2

UpSertData is call to the api where it pushes the data to the api for uploading and inserting @rrk Can you explain what you mean by putting it separate, how that can help?, its already doing a batch upload

If the "upsert" is happening in the same database, then running a query to read data from one table and update another is infinitely faster done directly on the database.

SQL update from one Table to another based on a ID match

Example from that answer:

UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;

If the data is coming from your system and being sent to another system's API, it may be better to send that data over one record at a time or fewer at a time. You can create a Scheduled Task in CF Admin that pings your script every minute or so, sending a much lower volume of data at a time. It will take more requests, but could likely complete in a much more timely manner than trying to shove a large volume of data over at once.

For example, I had a product sending over 10k records to mine at a time. It would often timeout, failing to sync data. That product was already sending single record updates to a microservice, so we updated my product to subscribe to the same microservice. I would get individual record updates, but my system (CF based) could process 10k requests quickly and without fail, as opposed to trying to process (and often failing to process) one large request.

Adrian J. Moreno
  • 14,350
  • 1
  • 37
  • 44
  • i am sending 500 in one loop – Jianxee Jan 14 '22 at 22:34
  • want me to send 10 records, but if 10, then loop will run i do not know how many times to insert 15000 records and it will definately timeout – Jianxee Jan 14 '22 at 22:40
  • You'd need to keep track of records already sent. Also, what about the question above- is "...the "upsert" ...happening in the same database"? – SOS Jan 15 '22 at 22:52
0

If you don't care about how long your script runs but just want to avoid the timeout, you can increase the time limit via <cfsetting>. E.g. you can set the timeout to 10 minutes by writing this:

<cfsetting requesttimeout="600">
Sebastian Zartner
  • 18,808
  • 10
  • 90
  • 132