Solved

Classic ASP Loop pegging CPU

Posted on 2011-03-18
2
713 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Losing network connectivity 8 77
P2P and MPLS 3 46
Unknown security group 2 59
Transfering files via a single Cat5 between two DOMAIN computers. 14 77
If your business is like most, chances are you still need to maintain a fax infrastructure for your staff. It’s hard to believe that a communication technology that was thriving in the mid-80s could still be an essential part of your team’s modern I…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
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…
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…

867 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

21 Experts available now in Live!

Get 1:1 Help Now