Processing large batch - best practice

Hi Gary,

I have 2 questions and a remark, to begin with.

Question: Are the 1000 transactions independent or are they nested (as sub-transactions) within one overall transaction? 

Question: You mentioned timeout. So are you using cflocation to enable you to set a requestTimeout at page level?

Remark: If not, then the cflocation strategy will do the same thing as cfloop, but less efficiently.

The end tag </cfloop> passes command back to the start tag <cfloop> within the same page context. Whereas <cflocation> will run the Application file, then everything preceding the loop, before passing command to <cfloop>.

Thanks for the great recommendations. I'll read up on CFTRANSACTION and will play around with that. Perfect timing since I just took an online SQL class and discovered rollbacks etc. I just didn't know how to use it in CF.

To address some of Charlie's questions... I'm using msSQL 2016. Yes, when I mention the page timing out I am referring to the page timing out from looping over too many transactions. I know I can set the requesttimeout on the page my concern is x seconds may be good today but as the number of transactions grows over months that would need to be adjusted. Is there any danger in setting this to some absurd number of seconds?

I have a process that runs at the end of the day the scans the day's invoices from Quickbooks for invoices flagged autopay and creates a new table with all the data needed to run the transactions merged into a single table. Next, I loop over the new table and send the friendly "hey your card is going to be charged $x tomorrow." At the end of the next day is the process in questions here. I need to loop over this table and actually charge the customers card and I want to be as careful as possible not to have it crash and charge the card twice because it needs to start over or fail and not finish charging all the cards. Since this table is only being used to process the autopay from the previous day being locked is not an issue. That's why I thought it would be good to delete the row after it completes. It's never needed after the card is charged.

Thanks!

There are a couple of things I'd recommend. My recommendations might be a little different from Charlie's, though that doesn't mean his are wrong! First, I'd try to figure out what are the problematic things that might happen if your operation fails in mid-process, and think about how you can address those things. Second, I'd try to figure out what are the problematic things that might happen if your operation doesn't fail, and likewise think about them too.

So, CFTRANSACTION will let you treat the whole batch as one single operation. That's good, in that if part fails, the whole thing will fail and you can rerun it from scratch and not worry about losing track of where you are. But there are some problems you might run into with CFTRANSACTION. One, it will effectively lock all access to the tables in question - after all, that's literally what it's supposed to do. If you expect to have other interactions with those tables, they'll be blocked until the CFTRANSACTION is done, or vice-versa. If your CFTRANSACTION will take a long time, those other operations may simply time out. We don't know enough about your data (like Charlie said) to really address this part, but it would be unfortunate if some of your sales failed because you were busy with this operation. Another approach would be to have some field you can use as a flag, and maybe use a single query to act on one row at a time, based on the value of the flag. So you could have one query that fetches all of the rows with the flag field not yet set, then loop over those, then have a nested query within the loop that does whatever is needed to each individual row. You may or may not need CFTRANSACTION in that case, as a single query is effectively a transaction all by itself. Again, we'd need to know more about what's going on.

But another problem you might have is that this whole operation simply takes a long time. Unfortunately, iterating over a bunch of records in CF is just going to be a lot slower than doing the same directly from SQL. So, you could try to move the whole operation into a single stored procedure, which would have its own loops, etc. This requires a little more SQL knowledge than a lot of developers have (or even need most of the time) but it could definitely improve overall performance.

One other thing to note: I see you have a CFLOCATION inside your code. This will break out of your CFIF or CFLOOP, and of course it'll take you to another page. You really don't want to do this, as a rule, as it's going to make processing a bunch of records more complicated than it needs to be.

Finally, this kind of batch processing is very well suited to CFTHREAD. Instead of waiting for the operation to complete before your code continues, you can use CFTHREAD to launch a second thread in the background that does the work, then your code can continue doing something - even if that something is as trivial as just letting the user know that things are happening in the background. Of course, if you try to capture the completion of that thread, you'll have to write some more complicated code to see when it's finished, but many times you don't even need to do that.

Dave Watts, Eidolon LLC

I just coded a very similar scenario and used a loop and wrapped the processing of each itteration in cftransaction.

Something like

loop over ToBeProcessed

    transaction start

    try {

      Process Stripe transaction

      Delete ToBeProcessed

      transaction commit

    } catch {

        transaction rollback

        rethrow

    }

end loop

This commits each transaction as it completes and if it fails inside the try block the rollback will undo all parts of the one transaction so the ToBeProcessed table is left with only uncompleted transactions so can you just run the script again, after fixing what caused the failure.

To add to what Charlie has suggested (and the cftransaction tag was made specifically for situations like this), you can also get a querylist of the IDs and use that to delete the records from the toBeProcessed table, after the processing is done, within the cftransaction tag.

V/r,

^ _ ^

Gary, I'd think we'd need more info to make a really effectice recommendation,. For instance, do you mean that you would originally have looped over this qb data and used sql (in cfquery or cfstoredproc, etc) to then insert/update/delete records in some DB of your own? If so, then I understand that your concern is what happens if you don't finish all the processing.

But then there are two solutions for that:

• this is where the cftransaction tag can shine. You could wrap the entire loop in that tag, and that would cause CF to tell the DB to regard all the sql processing within that outer cftransaction tag to be treated as a single unit, such that if any failed, they would all fail, and the DB should "roll back" (undo) the processing that was done (within that outer cftransaction tag).
  • You should test things to make sure your db DOES do that, using some sample data and introducing an error, before relying upon it. Also, there can be some edge cases where doing too many actions within that outer tag could potentially hold up other procesing (in the db, if the db server decides it needs to introduce lock contention. But really for most people, the benefits of using cftransaction outweigh such outlier risks.
• Otherwise, you could address your need by creating some sort of tracking of how many records you have processed, so that you could know (if it failed) that you could start over at that point.
  • You could track that count in any of many ways, including a properties file, a db table, a log, or even a shared scope (like session or application or server, though those would be lost of CF were to restart somehow in the midst of processing).

Finally, as for your concern over "timeouts" (from looping over too many operations), I asume you mean the CF page request timeout (which can be set either at the server/admin level, the application level, or the page level). You can override it at the page level, by using cfsetting requestttimeout="some seconds", on this page doing the processing.

Let us know if that helps, or if for some reason it does not.