Performance issues

[onressy]

Greeting fols, i have tracked down, thru stress testing, this piece of code that is giving performance issues, I was wondering what suggestion can be given to make it more robust, thanks!

Set objProdRS = objConn.Execute(strSQL)

' 2 May 2004, Moving objProdRS into another data structure so as to 
' not have 2 record sets open at the same time.  (getting production performance issues)
Dim arrProduct()
Dim strGetProductDetailsCode
strGetProductDetailsCode = ""
ReDim arrProduct(intProductCount*2)
intProductCount = 0
Do Until objProdRS.EOF
	arrProduct(intProductCount) = objProdRS("ProductID")
	arrProduct(intProductCount + 1) = objProdRS("ProductName")
	strGetProductDetailsCode = strGetProductDetailsCode & "case """ & arrProduct(intProductCount) & """:" & vbNewLine
	strGetProductDetailsCode = strGetProductDetailsCode & "	strResult = """ & Replace(arrProduct(intProductCount + 1),"""","'") & "<~>" & objProdRS("MaxGuest") & """; break;"		
	intProductCount = intProductCount + 2
	objProdRS.moveNext
Loop
objProdRS.Close
Set objProdRS = Nothing

 

[Chopstik]

Something that has been pointed out before is to use GetRows which will cut down on being connected directly to your database but will still maintain your data for you.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Tarwn]

And GetRows was 7-11 times faster than using the recordset back when I benchmarked those two.

And concatenating together a long string in a loop is never pretty in VBScript, as it handles memory management poorly during concatenation.

 

[onpnt]

That was benchmarked also. Concatenation that is. I'll see if I can find the threads. they are always good reading no matter how many times I've read them


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[onpnt]

thread333-939632 <-- do it yourself with Tarwn's helping hand
thread333-460972
thread333-925852 <-- download the beast


____________ signature below ______________
General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[Tarwn]

Or perhaps the longest benchmarking thread we had yet: thread333-941032

I don't know whats more sad, that we spent that many threads benching scripts or that the scripts were just to implement a VBScript version of ProperCase