Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Classic ASP Loop pegging CPU

Posted on 2011-03-18
2
Medium Priority
?
729 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 16

Accepted Solution

by:
AlexPace earned 2000 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

Optimum High-Definition Video Viewing and Control

The ATEN VM0404HA 4x4 4K HDMI Matrix Switch supports 4K resolutions of UHD (3840 x 2160) and DCI (4096 x 2160) with refresh rates of 30 Hz (4:4:4) and 60 Hz (4:2:0). It is ideal for applications where the routing of 4K digital signals is required.

Question has a verified solution.

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

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
This article is in regards to the Cisco QSFP-4SFP10G-CU1M cables, which are designed to uplink/downlink 40GB ports to 10GB SFP ports. I recently experienced this and found very little configuration documentation on how these are supposed to be confi…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

661 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