[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

How can I speedup my code?

I'm retriving a textfile in binary format to a string buffer. The content of file is simply user list in comma delimited format. I need to add a prefix to each user and dump it in textbox.  Here is the Code:

  dim varArray  as varient
  dim intFree   as integer
  dim strBuffer as string

  intFree = FreeFile
 
  intFileBuffSize = FileLen(cmdImport.FileName$)
  strBuffer = Space(intFileBuffSize)
 
  Open cmdImport.FileName$ For Binary Access Read As #intFree
    Get #intFree, 1, strBuffer
  Close #intFree
 
  varArray = Split(strBuffer, Chr$(44), , vbTextCompare)
  Do While (1)
    strBuffer2 = strBuffer2 & cmbSystems & "\" & varArray(i) & ";"
    i = i + 1
  Loop
  txtAccounts = strBuffer2

Loading a File is extrimly fast, however, spliting and modifing users takes an extreme amount of time, aspecaily when I have 1000 or more entires.

Please does anyone have any suggestions.

Thank you
0
MariuszK
Asked:
MariuszK
1 Solution
 
RMatzkaCommented:
Well, I'm getting the impression that with this code you could be waiting forever. The condition for exiting your loop is that the expression (1) evaluates to False, which will never occur. Instead of the Do While Loop, you better use
For i = 0 to UBound(varArray)
...
Next i

And if indeed you need to do something for speed, the only thing you can do is declare varArray() as String, which is faster than Variant.
0
 
MariuszKAuthor Commented:
Yeah, I totaly forgat about it. This is changeg now to:

 i = LBound(varArray)
  Do While i < UBound(varArray)
    strBuffer2 = strBuffer2 & cmbSystems & "\" & varArray(i) & ";"
    i = i + 1
  Loop

Thanks for pointing this out, but this still does not solves my problem. ( it was positing typo)
0
 
inthedarkCommented:
Here are some tips to improve speed:

dim strArray  as String ' tip 1 - using string will be faster
dim intFree   as integer
dim strBuffer as string

intFree = FreeFile
Open cmdImport.FileName$ For Binary Access Read As #intFree
strBuffer = Space(lof(intfree)) ' tip 2 - no need for FileLen
Get #intFree, 1, strBuffer
Close #intFree

' tip 3 - binary compare is faster
' you don't need textcompare unless you need
' A-Z to be same as a-z
' in your case 44 is below A so you vcan use binary
strArray = Split(strBuffer, Chr$(44), , vbBinaryCompare)
strBuffer = "" ' tip 4 - release memory


Dim lngMax as Long ' tip 5 - longs are faster than integers

lngMax = Ubound(strArray) ' tip 6 - long is faster for end of loop
Do While I< lngMax
   ' tip 7 + is faster than &   
   strArray(i) = cmbSystems + "\" +   strArray(i) + ";"
   i = i + 1
Loop

' tip 8 - join is the oposite to Split and faster
txtAccounts = Join(strArray,"")

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
inthedarkCommented:
Sorry I missed the braces.  String array is the result of a split an will be much faster then a variant array.

dim strArray()  as String ' I forgot the ()
0
 
inthedarkCommented:
Dim I as Long will also improve speed over dim I as Integer.

This is becuase the CPU will convert an Integer to a Long before any math op.
0
 
inthedarkCommented:
The routine I posted may be upto 7164% faster.  Take a look at the following code.  Loop1 works very fast, loop2 is very slow.

Loop1 did 240024
Loop2 did just 3567 in the same time.

Hope this helps:~)


Private Sub Command1_Click()

Dim strOut As String

Dim strTest As String
Dim i As Long

Dim StartTime As Single
Dim lngLoop1Count As Long
Dim lngLoop2Count As Long
ReDim strDummy(20000)
Dim TotalLoop1Time As Single

strTest = "This is a test string"

' Loop 1 using array, then join

StartTime = Timer
Do While Timer - StartTime < 1
    If lngLoop1Count > UBound(strDummy) Then
        ReDim Preserve strDummy(lngLoop1Count + 10000)
    End If
    strDummy(lngLoop1Count) = strTest
    lngLoop1Count = lngLoop1Count + 1
Loop

strOut = Join(strDummy, vbCrLf)
TotalLoop1Time = Timer - StartTime

strOut = ""
DoEvents

StartTime = Timer
Do While Timer - StartTime < TotalLoop1Time
    strOut = strOut + strTest + vbCrLf
    lngLoop2Count = lngLoop2Count + 1
Loop
strOut = ""
DoEvents



MsgBox "Loop 1 did " + CStr(lngLoop1Count) + " loops" + vbCrLf + "Loop 2 did " + CStr(lngLoop2Count) + " loops"

Clipboard.Clear
Clipboard.SetText CStr(lngLoop1Count) + "  " + CStr(lngLoop2Count)

End Sub
0
 
AdsBCommented:
inthedark's suggestions are all good ones.

I had an another thought:

You don't need the loop at all.  Instead just use the VB "replace" function as shown below:-

txtAccounts = cmbSystems & "\" & Replace(strBuffer, ",", "," & cmbSystems & "\")

This one line does exactly what you want in a single statement.  You can call it as soon as you have read the contents of the file into the strBuffer variable.  The rest of your code can go!

Hope this helps

AdsB
0
 
inthedarkCommented:
Further information - when adding to an array it is better to oversize the array to start with and if you need to increase the size, increase size in large chunks.  But  before using join you may wish to reset the size of your array to theactual number of elements:

ReDim Preserve strDummy(lngLoop1Count-1)

txtAccounts = Join(strDummy, vbCrLf)

0
 
MariuszKAuthor Commented:
WOW, thank you for all the responses.  I would never expect to get so many of them (GREAT ONES) in such of short time. :-)

Thank you,  I'm will test this as soon as I get to my PC
0
 
inthedarkCommented:
Did it work for you MariuszK, or do yo need more help?
0
 
inthedarkCommented:
MariuszK what happened?  Don't tell me it worked so fast you got promoted and now just play golf all day?
0
 
CleanupPingCommented:
MariuszK:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Experts: Post your closing recommendations!  Who deserves points here?
0
 
DanRollinsCommented:
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer
0
 
YensidModCommented:
Question is PAQ'd and no points refunded.

YensidMod
Community Support Moderator @Experts Exchange
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now