Classic ASP Loop pegging CPU

I am having a problem with a loop that is doing a fairly large SQL bulk insert pegging the server CPU at 100% usage.

The script (code below) is creating custom promo codes that it then inserts into the DB. It works fine with small batches of a few hundred or even a thousand or so, but when we try and create 5,000 codes it generally hangs the server. Regardless of the number being created I notice that every time the script runs the server CPU is pegged with the IIS process for the website chewing 98-99%.

In order to make it more efficient I changed to a SQL bulk insert method. Then I also split the loop up into batches of 1000, thinking that might help, but no luck.

To insert 5,000 records into the database (five 1000 iteration loops to build the file, then one bulk insert per big loop) took 16 minutes!!  That makes no sense to me.

How can I do this more efficiently? And why is a single W3 process running one loop of not very difficult code pegging the CPU?

' Split the insert loop into batches of 1000
 BigLoop = Int(SU_Qty/1000)+1
 For bl = 1 to BigLoop
  	SQL = ""
	If BigLoop = 1 then SmallLoop = SU_Qty Else SmallLoop = SU_Qty-(1000*(bl-1))
	If SmallLoop > 1000 then SmallLoop = 1000
	'response.Write("SmallLoop: " & SmallLoop & "<br>")	
	For i = 1 to SmallLoop
		' Create unique Promo Code
		Randomize()
		Rand1 = FormatThreeDigits(Int(Rnd()*1000))
		If i > 9 then Rand1 = FormatTwoDigits(Int(Rnd()*100))
		If i > 99 then Rand1 = Int(Rnd()*10)
		ThisPromoCode = Year(NOW) & FormatTwoDigits(Month(NOW)) & FormatTwoDigits(Day(NOW)) & _
						Rand1 & i & FormatThreeDigits(Int(Rnd()*1000))
		'response.Write("ThisPromoCode: " & ThisPromoCode & "<br>")	

		' Create delimited text file (to use for SQL Bulk Insert)
		SQL = SQL & i & "~" & _
					ThisPromoCode & "~" & _
					Promo_Desc & "~" & _
					Promo_Type & "~" & _
					"0" & "~" & _
					Promo_Start & "~" & _
					Promo_End & "~" & _
					SU_BatchID & "~" & _
					SU_BatchType & "~" & _
					SU_Qty & "~" & _
					SU_Customer & "~" & _
					SU_CustomerEmail & "~" & _
					SU_FilterType & "~" & _
					SU_FilterValue & "~" & _
					"0" & "~" & _
					"" & VbCrLf
						

	Next

		' Save the temp BULK INSERT file to disk
		SQLBULKFileName = "SQL_BULK_Tempfile.txt"
		SQLBULKFilePath = Server.MapPath("/Content/TempFiles/") & "\" & SQLBULKFileName
		Set objFSO = CreateObject("Scripting.FileSystemObject")
		Set objFile = objFSO.CreateTextFile(SQLBULKFilePath)
		objFile.Close
		Set objFile = Nothing
		Set objTextFile = objFSO.OpenTextFile (SQLBULKFilePath, 8 , True)
		objTextFile.WriteLine(SQL)
		objTextFile.Close
		Set objTextFile = Nothing
		Set objFSO = Nothing

		' Execute the BULK INSERT from the temp file
		con.Execute("BULK INSERT Promo_Codes FROM '" & SQLBULKFilePath & "' " & _
					"WITH (FIELDTERMINATOR = '~',ROWTERMINATOR = '\n')")

  Next

Select allOpen in new window