...and be careful what you ask for.

I was recently asked to take some VBScript code from an ASP page and transform it into ASP.NET. Naturally, it being already in VBScript, it made good sense to do the re-write in VB.NET. I was also asked to see what I could do in terms of optimizing it.

The first thing I noted was that it was using ODBC to connect to a back-end database. The second thing I noted was that the values from the returned dataset where being stored in an array, then, using the Join() function, were concatenated into a string.

I figured that I could use a DataReader object, and concatenate the string on the fly. In order to make sure that I would be returning the right results, I wrote a simple Winform app to display the results in a text box.

Now there were over 2,200 records, so it was a pretty big string. Anway, I fired up the Winapp and it took what I thought was an unrealistically long period of time. After about 30 seconds, the results finally appeared. This wasn't acceptable.

So, I fired up VFP 8.0, used ODBC with SPT, and wrote a form virtually identical to the WinForm. When I ran it, the results came back it a tad over 2 seconds.

Going back to VB.NET I decided to find out what the bottleneck was. To make a long story short, it wasn't the DataReader or anything that I expected. It was the concantenation.

To address the second part of the thread's title (...be careful what you ask for.), it would seem to me that if VFP were included in the CLR (which, thankfully, it isn't and, hopefully, won't be) it would suffer the same performance deggradation in its string handling that VB now suffers from.

As for the first part of the title, here's the tip: When doing string concatenation with a large number of strings, store them in an array and do the concatenation with Join(). I went back to that approach (using a DataSet), and, sure enough the performance improved nearly tenfold...but still not as fast as VFP.:-)