One thing I’ve found myself doing more and more in ColdFusion is using it for it’s integration powers to parse data, or do batch performing tasks.  With it’s Java underbelly, ColdFusion does most things great, and if you want even more performance, tie into the Java layer!

I come from the group of people who pursue the dream that computers and technology should make people’s lives easier.  That’s why ruthless automation is at the center of most startups, or businesses that are growing.  Keep people dealing with people — what they do best, and let your system handle the details for you!

1) Maintain a file upload table (Parent table). For every file you upload you should be able to keep a list of each file and what status it is in (uploaded, processed, unprocessed)

2) Temp table to store all the rows of the data file. (child table) Import the entire data file into a temporary table. Attempting to do it all in memory will inevitably lead to some errors. Each row in this table will link to a file upload table entry above.

3) Maintain a processing status – For each row of the datafile you bring in, set a “process/unprocessed” tag. This way if it breaks, you can start from where you left off. As you run through each line, set it to be “processed”.

4) Transaction – use cftransaction if possible to commit all of it at once, or at least one line at a time (with your 5 queries). That way if something goes boom, you don’t have one row of data that is half computed/processed/updated/tested.

5) Once you’re done processing, set the file name entry in the table in step 1 to be “processed”

By using the approach above, if something fails, you can set it to start where it left off, or at least have a clearer path of where to start investigating, or worst case clean up in your data. You will have a clear way of displaying to the user the status of the current upload processing, where it’s at, and where it left off if there was an error.

If you have any questions, let me know.

Other thoughts:

1. You can increase timeouts, give the VM more memory, put it in 64 bit but all of those will only increase the capacity of your system so much. It’s a good idea to do these per call and do it in conjunction with the above.
2. Java has some neat file processing libraries that are available as CFCS. if you run into a lot of issues with speed, you can use one of those to read it into a variable and then into the database
3. If you are playing with XML, do not use coldfusion’s xml parsing. It works well for smaller files and has fits when things get bigger. There are several cfc’s written out there (check riaforge, etc) that wrap some excellent java libraries for parsing xml data. You can then create a cfquery manually if need be with this data.