[RESOLVED] optimization tip

Can this piece of code be optimized?
trying to pass some RecordSet values to an arryay, removing line feed and return carriage

Code:

---

        For i = 0 To 10
          If rs_Bills.Fields(i) <> Null Or rs_Bills.Fields(i) <> vbNullString Then
                ValTMP = Replace(CStr(rs_Bills.Fields(i)), Chr(13), vbNullString)
                ValTMP = Replace(ValTMP, Chr(10), vbNullString)
                Valores(i) = ValTMP
                ValTMP = vbNullString       
            Else
                Valores(i) = vbNullString
            End If
        Next i

---

:eek: Really desperate about optimization...

It only runs 11 times, so you probably won't find a major difference in speed - but if this is inside another loop, it will make a minor improvement in the long run.


The If statement can be improved.. but before that, don't use "<> Null" as it wont actually work properly (instead use Not(IsNull(rs_Bills.Fields(i).Value)) ).

The way to make it faster/shorter is to use a little trick that is often used with Null - simply concatenate the field value with an empty string, and the value that is returned will be an empty string (unless the field contains characters); this means you only need to perform one check instead of two. eg:

Code:

---

If rs_Bills.Fields(i).Value & vbNullString <> vbNullString Then

---

Another improvement is to replace the calls to the Chr() function with variables which you set at the start of the code (before the loop), or even better use built-in constants instead (vbCr is the same as Chr(13) and vbLF is the same as Chr(10)).

When using string functions (like Chr and Replace), put a $ after the function name (unless that gives you an error), as the $ means return a String, rather than a Variant with a String sub-type (and Variants are slow).

And finally.. don't run code that you don't need to. The second Replace should be stored directly into the array, rather than being put into a variable which is put into the array. Better still, nest the calls to Replace so that ValTMP isn't needed at all.

this may run up to 300000 rows from a recordset.
My process takes 7 hours and every second that i take is like money for me ;)
I'll try your tips.
Thank you

In that case it could save a lot of time - each millisecond saved per iteration will turn out to be several minutes overall.

You may well find that you can get much larger speed improvements by using the database engine instead - but how/if you can do that depends on the database system, and what you are trying to achieve (which may include the SQL statement and table structure, and what you are doing with the array after this code).

Using the Recordset.GetString method you can get rid of the For Loop and If statement. You can even just code it all in one line.

The valores array must be declared as Dim valores() As String.
GetString advances the recordset pointer so there is no need to call MoveNext.

Code:

---

Do Until rs.EOF
    valores = Split(Replace(Replace(rs.GetString(, 1, vbTab, , vbNullString), vbCr, vbNullString), vbLf, vbNullString), vbTab)
  '...
Loop

---

So, right now this is what i got:

Code:

---

For i = 0 To 10 Step 1
    If rs_Bills.Fields(i) & vbNullString <> vbNullString Then
        Valores(i) = Replace$(Replace$(CStr(rs_Bills.Fields(i)), vbCr, vbNullString), vbLf, vbNullString)
    Else
        Valores(i) = vbNullString
    End If
Next i

---

Can this be more optimized?
Thank you.

__
Edit:
My processing time shrinked to 50min.
http://vbforums.com/showthread.php?t=499231

It is much faster to check for string length using LenB instead of comparing to vbNullString:

If LenB(strTemp) <> 0 Then


Replace is slow even when it doesn't do anything. It is much faster to check if there is a need for calling it in the first place:

Code:

---

strTemp = CStr(rs_Bills.Fields(i))
blnCr = InStr(strTemp, vbCr) > 0
blnLf = InStr(strTemp, vbLf) > 0
If blnCr And blnLf Then
    Valores(i) = Replace(Replace(strTemp, vrCr, vbNullString), vbLf, vbNullString)
ElseIf blnLf Then
    Valores(i) = Replace(strTemp, vbLf, vbNullString)
ElseIf blnCr Then
    Valores(i) = Replace(strTemp, vbCr, vbNullString)
Else
    Valores(i) = strTemp
End If

---

It is longer, but should be much faster if there aren't line changes in every single line.

I'm pretty satisfied with all replys.
I'm going to mark this thread as resolved.

Thank you si_the_geek, brucevde and Merri.