Faster Way to Get String

I guess it does it OK I'll add more data to my example of what I have.  The class module appears to do about the same thing.
I ran  the class example and it started out ok then got slower and slower, then it ran out of memory on my 128 MB Machine. then the Program took a massive dump and disappeared.  WinNT


Here is a more detailed Example

dim ls_Row as string            ' Will contain all the Data for a Row in a resultset
dim li_LenSoFar as long        ' Will contain the length of the String's useful characters
dim lr_Res as rdoResultset      '= a resultset <or recordset doesnt matter>

'INIT ----------
ls_Row = String$(65000, vbKeySpace) 'Gimmie some space for string.
                              'Assume max 255 columns * Max 255 max length per column

'MAIN ----------
if not lr_Res.EOF then
    For x = 0 To lr_Res.rdoColumns.Count - 1    ' go through each column in the resultset
        If IsNull(lr_Res(x)) Then
           Mid$(ls_Row, li_LenSoFar + 1, 7) = "<NULL>,"   ' if null then give me this instead
           li_LenSoFar = li_LenSoFar + 7               ' add to the count of the length
        Else
           ls_Col = CStr(lr_Res(x)) & ","
           Mid$(ls_Row, li_LenSoFar + 1, Len(ls_Col)) = ls_Col      'go to the first Available space and add the text
           li_LenSoFar = li_LenSoFar + Len(ls_Col) + 1            ' add to the count of the length
        End If
    Next x 'Next column
end if

'RESULTS -------
ls_Row = left$(ls_Row,li_Lensofar - 1) ' trim the last comma
msgbox ls_Row                      ' msgbox of a comma delimited row


'End Example

I had read that Byte Arrays would be faster.

the above example might return something like
"0001,Test-Dept,Bill Smith,555-1212"

before i read your enlarge example - i'm reffering to
your short original question.
FIRST - you can ommit the Cstr fonction from the loop.
10000 times calling that F is time, and you don't need that
in VB.
SECOND - CPU and memory works harder and harder to retrive
the string again and again, and its getting longer makes things harder.
if you run into such prob - you've better write every 500-1000 bytes or so into an array of strings.
you can prepare that array from advance, or you can RedimPreserve it as the proccess needs. every 500 or so - add a new string to the array.
THIRD - if it's really really really BIG - consider writing
all every 500 0r 1000 to FILE. Don't shout NO !!! FILE is slow allright, but as you write the 1000 to the file - you free MEMORY as CPU, and you go on fresh new.

This may not be what you are looking for because it still does use the ampersand bu it runs about 20 times faster using simply better techniques than your method here it is:

Private Sub Command1_Click()
Dim x As Long
Dim ls_String As String
Dim ls_tmp As String
Text1 = Time
Text1.Refresh

For x = 0 To 99
    ls_tmp = ""
    For y = 1 To 100 Step 10
        ls_tmp = ls_tmp & CStr(x * 100 + y) & "," & CStr(x * 100 + y + 1) & "," & CStr(x * 100 + y + 2) & "," & CStr(x * 100 + y + 3) & "," & CStr(x * 100 + y + 4) & "," & CStr(x * 100 + y + 5) & "," & CStr(x * 100 + y + 6) & "," & CStr(x * 100 + y + 7) & "," & CStr(x * 100 + y + 8) & "," & CStr(x * 100 + y + 9) & ","
        Text2 = Str(x)
        Text2.Refresh
    Next y
    ls_String = ls_String & ls_tmp
Next x
ls_String = Left$(ls_String, Len(ls_String) - 1)

Text2 = Time
Text2.Refresh
Stop
End Sub

I put a STOP so you could see the string is the same as your original coded string.  The two techniques is to append to shorter strings then every so often append the short string to the large one.  This prevents the sluggishness of moving the big string thousands of times into itself as it reaches the full size later on.  Also notice that if you know what is going to be appended as we do that it is quicker using math to append ten at a time instead of one by one.  More code but huge speed increases doing the same thing you did very slowly with a single loop.

Most likely the only other answer will be to use something other than VB to accomplish this such as a DLL as with the Crescent QuickPak includes a DLL with many high speed functions you can call with functions similar to an API call.  

One last idea would be to use a control such as a richtext box or other box which can hold a huge amount of text and may provide for a pointer as to tell it where to insert your appended text.  This may use faster machine code or 'C' code to do the append insert where you can then grab the entire contents and assign to a string when done.  If its a visible textbox you may need to make it invisible to speed it up so it doesnt try to display each append.

1. It is very slow to loop through recordset. You should use GetRows method of your recordset - array looping is much faster.
2. Calculate the total size of your string - don't do this in chunks, appending 15K many times IS concatenation. You'll probably need two passes - first for getting total length, second for a real job.
3. Allocate space by using:
    bigstring = Space(totallength)    ' String(totallength, 32) is not good for big (many MBs) strings
bigstring or big array must be declared at module level to avoid stack error.
4. If you ommit the Cstr fonction, VB will still have to convert number to string. There is no speed difference.

See also my 'fast replace' code (10 pts):
http://www.experts-exchange.com/Q.10189972

' 1-10000 code
Option Explicit
DefLng I-Z
Private Declare Function GetTickCount Lib "kernel32" () As Long

Private Sub Form_Click()
    Const delim As String = ";"
    Dim x, tim0, maxlen, pos, i, lb, ub
    Dim ls_String As String
    ' start timer
    tim0 = GetTickCount
    ' calculate length of our string
    maxlen = 9 * 2                 ' 1...9
    maxlen = maxlen + 90 * 3       ' 10...99
    maxlen = maxlen + 900 * 4      ' 100...999
    maxlen = maxlen + 9001& * 5    ' 1000...10000
    ' create new string
    ls_String = String(maxlen, ";")

    pos = 1
    For i = 1 To 4
        lb = Choose(i, 1, 10, 100, 1000)
        ub = Choose(i, 9, 99, 999, 9999)
        For x = lb To ub
            Mid(ls_String, pos, i) = CStr(x)
            'Mid(ls_String, pos + i, 1) = delim
            pos = pos + i + 1
        Next
    Next
    Mid(ls_String, pos, i) = CStr(x)
    ' report time
    Print (GetTickCount - tim0) / 1000 & " seconds"
End Sub

Try the following (or something like it, using the GetString method), it should be the fastest way of getting a recordset in a string by far:

ls_Row = ls_Res.GetString(adClipString, , ",", ",", "<NULL>")

This also gives you the same result with only one line of code.

Good luck,
Jeremy