How to run CFLOOP in a batch of 100

Forum|Forum|15 years ago
June 15, 2010
2 replies
1689 views

I need to loop over a logic for each record I queried from a table once a day

Each day our dept. may receive a different number of records. Today we may get 100 new records, tomorrow may be 5000 and the next day again may be none.

My application crashes whenever we get 5000 or more records; so, I plan to loop in a batch of 100, for example, if today I get 5000 records, I want to run my app. 50 times, each process is looping 100 records. Is there a way to do this?

Getting started

This topic has been closed for replies.

D

Dan_Bracuk

Inspiring

Why don't you run the logic on all of them at once instead of one at a time.

ilssac

Inspiring

alecken wrote:
Is there a way to do this?

Several!

<cfthread...>

<cfcomponent...>

User defiend functions.

Custom Tags

Gateways

HTML Refreshes.

A

aleckenAuthor

Known Participant

I'm having a tough time understanding how can I tell CF to process 100 records on each loop when I have 5000 records, for example.

In my app., the first code to run is querying this table and then use the query result to process the logic in a looping fashion.

If I get 100 records or less, everything ran just fine BUT not when it returns 5000 records or more.The logic involves checking for each record whether it is pass or fail a verification kind of things.

In short, when I get 5000 records from that query, then I loop the first 100 records, how can I process the next batch start from record 101 to 200 then 201 to 300, and so on?

R

Reed_Powell-ttnmOb

Inspiring

The solution depends on just what is really causing your CF script to fail - can you give us information on the error you are receiving from CF? Are you running out of memory? If so, is it because the resultset from the query is so large, or is it because there is code in the CFLOOP that is causing memory usage to grow (creating variables, adding to a structure, displaying data to the output stream, etc)? Are you sure that CF is crashing, and not your browser?

If the problem is the overall memory footprint due to the size of the query, then one possible way to solve this is to create a var at the top of the script (variables.recordsToProcess), give it a value of around 100 to start with, and then write a query just to get the total record count (SELECT COUNT(*)). Now you can compute the number of passes needed to process all of the data, and can wrap most of your current code in a CFLOOP where the SQL query you are using now has been modified to return only one chuck of data (TOP #variables.recordsToProcess#), and the current CFLOOP only processes that chunk of data(TO="#variables.recordsToProcess#).

If the problem is due to an expanding memory footprint that you cannot get around, another conceptually similar way to solve this is If you pass a URL var to the script (url.recordsToProcess) that you can then use in the SQL query (TOP #url.recordsToProcess#), and in the CFLOOP (TO="#url.recordsToProcess#). Then write another script that does a query just to get the total record count, and have it loop over a CFHTTP call (that passes the URL var as a query param) to your current script for the appropriate number of times. If the reason for the memory expansion is because you are writing a lot data to the output stream, then as you execute each CFHTTP you can append its output to a file. that way you don't have all of the data sitting in CF memory at any one time. If you need to make the output stream accessible to the browser, then at the end of that script that is doing the cfhttp calls, just put a link to the file that the user can click on - or you could put in a CFCONTENT tag that points to the file.

You're not using CFDUMP in there to output data, are you? If so, then get rid of it and your problems will probably go away. Just spit out an HTML table with your data - you won't have all of the JS crappola that CFDUMP puts in there.

-reed

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded