Solved

Classic ASP Loop pegging CPU

Posted on 2011-03-18
2
710 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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Even if you have implemented a Mobile Device Management solution company wide, it is a good idea to make sure you are taking into account all of the major risks to your electronic protected health information (ePHI).
PRTG Network Monitor lets you monitor your bandwidth usage, so you know who is using up your bandwidth, and what they're using it for.
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…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now