Solved

Classic ASP Loop pegging CPU

Posted on 2011-03-18
2
716 Views
Last Modified: 2013-12-14
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

Open in new window

0
Comment
Question by:worthyking1
2 Comments
 
LVL 16

Accepted Solution

by:
AlexPace earned 500 total points
ID: 35166008
Building a long string in a loop can get dicey because of the way string concatenation works in VB.  I don't know if the problem would hit at only 1000 loops but I guess that depends on the length of the string and the amount of available RAM.  Basically the problem is that every time you want to add string "B" to the end of string "A"  ... The contents of A are moved into a temp variable "C" and then B is added to the end of C and then C is copied back into A.  This all happens behind the scenes but when A gets very large it really slows the loop.

The way around this is instead to declare an Array with 1000 elements.  Then do your For loop from 1 to 1000 and put one row in each element of the array.  At the bottom of your For loop after adding the last element use the Join() function to combine the array back into a string.  

The results can be dramatic.  I did some work for a guy a few years back that was building a file like this and it took 12 hours to run his process.  After changing to use arrays with Join it took less than 5 minutes.  Before the change he was able to run through 80% of the rows in 10 minutes, the next 10% in an hour, and then 11 hours for the final 10% and half of that on the last 1%.  Once the memory is saturated the computer is fuxxord and it has to swap to disk to do anything.

I don't know if this is the cause of your performance problem but, if it is, this is the way to fix it.
0
 
LVL 6

Author Closing Comment

by:worthyking1
ID: 35166783
Boom! Alex you are the man. I had no idea that VB worked that way with strings.  Once I rewrote the code my 5000 batch generation went from 16 minutes to 4 SECONDS!!!
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Don’t let your business fall victim to the coming apocalypse – use our Survival Guide for the Fax Apocalypse to identify the risks and signs of zombie fax activities at your business.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question