The original code actually called several stored procedures for each row. As you might expect, this caused both CF and the SQK server to grind to a halt. Looking at the stored procedures, I knew we could get more performance if I could take the logic of all the required SPs and combine them. I was able to rip out the logic and put it into 1 query. Now, my next issue was, would it be faster to loop over each row in the file and do a <cfquery> or, create the query inside of a <cfsavecontent> and then use that inside of one <cfquery> tag.
Each presented potential performance boosts and drawbacks. Using multiple queries, I could use <cfqueryparam> which could speed up the queries, but there would be thousands of calls to the DB. Using 1 Uber-query, I would only need to make 1 call to the database, but I would lose some performance due to the lack of <cfqueryparam>.
So, I did some tests. I imported 1000 rows of data at a time using each of the above mentioned methods. I made sure to test each against an identical DB, initially empty (to test inserts) and then with some data already in the DB (to test inserts and updates).
The winner? Looping over each row, and running a <cfquery> for each row. To be honest, I really wasn't sure which one would be faster, but what surprised me was how much faster multiple <cfquery> calls was. Running multiple <cfquery> calls was 2-3 times faster than running one big Uber-query, depending on the dataset. If it wasn't for time constraints in getting this project done, I would run another test, ripping out the <cfueryparam> tags from the multiple-query method and see if that is the major difference between the two. I might revist that if i get a chance.