?
Solved

How can I speedup my code?

Posted on 2003-02-27
14
Medium Priority
?
156 Views
Last Modified: 2010-05-01
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
Comment
Question by:MariuszK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 

Expert Comment

by:RMatzka
ID: 8036743
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
 

Author Comment

by:MariuszK
ID: 8036847
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
 
LVL 17

Expert Comment

by:inthedark
ID: 8037510
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 17

Expert Comment

by:inthedark
ID: 8037519
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
 
LVL 17

Expert Comment

by:inthedark
ID: 8037547
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
 
LVL 17

Expert Comment

by:inthedark
ID: 8037666
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
 
LVL 4

Expert Comment

by:AdsB
ID: 8037704
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
 
LVL 17

Expert Comment

by:inthedark
ID: 8040046
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
 

Author Comment

by:MariuszK
ID: 8041443
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
 
LVL 17

Expert Comment

by:inthedark
ID: 8155610
Did it work for you MariuszK, or do yo need more help?
0
 
LVL 17

Expert Comment

by:inthedark
ID: 8378672
MariuszK what happened?  Don't tell me it worked so fast you got promoted and now just play golf all day?
0
 

Expert Comment

by:CleanupPing
ID: 8940434
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
 
LVL 49

Expert Comment

by:DanRollins
ID: 8983848
Moderator, my recommended disposition is:

    Save as PAQ -- No Refund.

DanRollins -- EE database cleanup volunteer
0
 

Accepted Solution

by:
YensidMod earned 0 total points
ID: 9104544
Question is PAQ'd and no points refunded.

YensidMod
Community Support Moderator @Experts Exchange
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question