Lcase operation on array taking too long

Question

I'm working with a script designed to compare values returned from a form against values from a database dumped to an array, via GetRows. The purpose of the check is to compare form values against database values and only update the matching ids' rows in the database. I've seen this done with hidden variables in forms, but as we have quite a few users online at any given time, the values on the db end could change while a user was completing the form.

Currently, the code uses an inner and outer loop to run this comparison, with a temporary variable being assigned the current col/row from the aforementioned array. An lcase and trim operation are performed on the value to obtain the temporary variable.
This is causing a considerable performance drain, and I was wondering if the lcase/trim functionality could perhaps be performed during the creation of that array, rather than in a looping situation? Here's my code: **note: this utilizes the FastString Class for concatenation, thus the "FastString" and ".Append"

dim iRowLoop, iColLoop, zRowLoop, strChange, tempDbValsCase
Set strChange = New FastString
for iRowLoop = 0 to ubound(arrDbVals, 2)

    for zRowLoop = 0 to ubound(arrFormComplete)

        ''#****below line is what is causing the bottleneck, according 
        ''#****to a timer test
        tempDbValsCase = lcase(trim(arrDbVals(1, iRowLoop)))
       ''#****

        if (mid(trim(arrFormComplete(zRowLoop)),1,8) = trim(arrDbVals(0, iRowLoop))) AND (mid(trim(arrFormComplete(zRowLoop)),9) <> tempDbValsCase) then

            dim strFormAllVals
            strFormAllVals = arrFormComplete(zRowLoop)
            strChange.Append strFormAllVals & ","

        end if

    next

next

On the database side (MS SQL Server 2008), the table from which the array is derived through GetRows contains the bit datatype column "Complete". The lcase and trim operations are performed upon this column of the array. Does the bit datatype add any hidden characters in the output? Visually, I don't detect any, but when I compare a value of "True" from the form input against a value from the array that looks like "True," it doesn't match, until I run the lcase and trim on the "Complete" column.

You can use `TypeName(yourvariable)` to compare if the data from your database and data from your form are the same type. — stealthyninja, Jan 31 '12 at 22:42
I plugged in TypeName(var) and the result was 'boolean' and 'string'. The "Complete" column is boolean, while the form values are "String". I converted to CStr, but without an improvement in performance. — buck1112, Feb 01 '12 at 20:41

score 1 · Answer 1 · answered Jan 31 '12 at 22:39

1

Try

dim iRowLoop, iColLoop, zRowLoop, strChange, tempDbValsCase
dim iCount1, iCount2, match

Set strChange = New FastString

iCount1 = ubound(arrDbVals, 2)
iCount2 = ubound(arrFormComplete)

for iRowLoop = 0 to iCount1

    for zRowLoop = 0 to iCount2

        ' Assign array lookup to a variable '
        tempDbValsCase = arrDbVals(1, iRowLoop)

        ' ...and then perform operations on it one at a time '
        tempDbValsCase = trim(tempDbValsCase)
        tempDbValsCase = lcase(tempDbValsCase)

        ' Assign this array lookup to a variable and perform trim on it '
        match = trim(arrFormComplete(zRowLoop))

        if (mid(match,1,8) = trim(arrDbVals(0, iRowLoop))) AND (mid(match,9) <> tempDbValsCase) then

            strChange.Append match & ","

        end if

    next

next

answered Jan 31 '12 at 22:39

stealthyninja

10,343
11
51
59

Many thanks - I tried this in place of my previous code, but the performance time is still the same. Is there perhaps a way to convert the boolean to string at an earlier stage? – buck1112 Feb 01 '12 at 20:45
1

Short of using `CStr` or `CBool` to try to make both values of the same type, you might have to try a different approach altogether. Perhaps something like working with smaller sets of results and then asynchronously loading and processing the next set after the first one completes. The biggest contributor to the dip in performance are the nested `for` loops. If you could separate the two from each other, you should potentially see a great improvement in overall speed of the script. – stealthyninja Feb 01 '12 at 22:23
Thanks for your good ideas! I'm not sure how I could separate them, as they are doing the comparison. I had thought of adding another db column in which, upon insertion of a new row, a trigger could copy the value in the "Complete" column to a new column - same value but different datatype - "nvarchar" for example. I don't like having to do that, but if the datatype could exist before the selection process (to avoid conversion at that time), then it might be good. – buck1112 Feb 02 '12 at 22:29

Lcase operation on array taking too long

1 Answers1