Today I had the need to write code that would loop over a recordset, parse the data in each iteration, and then insert the transformed data into another table. Nothing I haven't done countless times, but this time I was using the query functions in cfscript to do my inserts. I knew the resulting code was going to be a bit slow (as inserting thousands of rows takes time), but when I ran it it was a LOT slower than I expected.
After adding some code to time the various parts of my routine, I discovered that the time it took for each insert transaction was steadily growing with each iteration of the loop, to the point where it was taking 500+ milliseconds per insert. But why?
Then I saw the problem. I had forgotten to invoke the query object's clearParams() function at either the beginning or end of my loop. Apparently ColdFusion will let you create a query parameter with the same name attribute using addParam() - as was happening in my loop - and not throw an error (which is what I would have expected to happen), but it leads to a performance issue with the SQL execution.
In the few times where I've reused a query object with different parameters, I've been careful to use clearParams(), but I simply overlooked it this time. Lesson learned.
