Thought Delimited

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.