Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 37 of 37

Thread: Optimize String Handling in Visual Basic

  1. #21
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by DRJ
    So what happens if we increase the iterations. Let's try 1 million iterations each.

    [vba]
    Option Explicit

    Sub StringTest()

    Dim MyTimer As Double
    Dim i As Long
    Dim j As Long
    Dim UpperLimit As Long

    Application.ScreenUpdating = False

    UpperLimit = 200

    For j = 1 To UpperLimit
    Range("A:A").ClearContents
    Range("B1").Value = "Double Quotes"
    MyTimer = Timer
    For i = 1 To 5000
    If Range("A" & i).Value = "" Then
    Range("A" & i).Value = 1
    End If
    Application.StatusBar = "Loop 1: " & j & " of " & UpperLimit & " (" & i & ")"
    Next i
    Range("B65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
    Next j
    For j = 1 To UpperLimit
    Range("A:A").ClearContents
    Range("C1").Value = "vbNullString"
    MyTimer = Timer
    For i = 1 To 5000
    If Range("A" & i).Value = vbNullString Then
    Range("A" & i).Value = 1
    End If
    Application.StatusBar = "Loop 2: " & j & " of " & UpperLimit & " (" & i & ")"
    Next i
    Range("C65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
    Next j
    For j = 1 To UpperLimit
    Range("A:A").ClearContents
    Range("D1").Value = "Len"
    MyTimer = Timer
    For i = 1 To 5000
    If Len(Range("A" & i).Value) = 0 Then
    Range("A" & i).Value = 1
    End If
    Application.StatusBar = "Loop 3: " & j & " of " & UpperLimit & " (" & i & ")"
    Next i
    Range("D65536").End(xlUp).Offset(1, 0).Value = Timer - MyTimer
    Next j
    Range("B" & UpperLimit + 2).Value = "=Average(B2:B" & UpperLimit & ")"
    Range("C" & UpperLimit + 2).Value = "=Average(C2:C" & UpperLimit & ")"
    Range("D" & UpperLimit + 2).Value = "=Average(D2" & UpperLimit & ")"
    Range("B").EntireColumn.AutoFit

    Application.ScreenUpdating = True

    End Sub
    [/vba]

    In my test with this code, Len came out on top with an average of .50704. The other two methods were less than 1% off.

    I am running this on Excel 2002 with Windows XP Home 3GHtz CPU with 1 GB Ram.

    So basically, choose you method of choice.
    I would like all to test this code on their machine and give us the results...
    The more the better....

    Can anyone help me to make a poll on this test results?
    I don't know how to make a poll on this...

  2. #22
    VBAX Tutor
    Joined
    May 2004
    Location
    Germany, Dresden
    Posts
    217
    Location
    My guess is, there are just too many factors beside the code fragment that influence the necessary time.
    Take Windows memory management for instance. How much is swapped, when will be the next swapping, what other processes are running etc.
    Maybe to really test this, you'll need a clean environment (so just Windos + Office, no Anti-Virus software, no indexing or other processes, reboot after each test, run each test many times (because of different conditions after each boot).

  3. #23
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Gentlemen!, Gentlemen!, What are you doing?

    vbNullString is a code construct. This has nothing to do with Excel. The gains, if there are any, are to be made when you have String variables set to, and compared to, vbNullString.

    I don't know how values are held in Excel cells, but I am not aware of an Excel equivalent to vbNullString and I would not expect any significant performance advantage when one half of your expression is a cell value. Also, I rather suspect that writing to the StatusBar rather distorts the measurements.

    I have just run a brief test and not seen any great difference but will do a proper one later.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  4. #24
    Dear,

    Great posting on faster execution.

    A coder should know these things.

    Thnks for posting


    ....

  5. #25
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    I think I was slightly hasty in my last response, sorry!

    What I should have said was that the Excel aspects of the test code seemed likely to take the lion's share of the execution time. Apart from anything else, Excel cell values are variants which adds extra complication to the picture.

    I have run some more tests (see below) which suggest that vbNullString is actually slower than anything else - and checking for a length of zero is the fastest. I can only guess at the reason for this but I'll try ..

    Nulls do not really exist in Excel at all, and they are not native to VBA. As I understand it, support for them (apart from in Access) is provided mainly for compatibility with other applications which may require them. My suspicion is that, on finding a null string when doing a string operation, the first thing that VBA probably does is to create an equivalent non-null (empty) string. I imagine, in this context, that determining the length of a null string is not considered an operation requiring strings, and that a little optimization is done.

    The code I ran was:
    [VBA]Option Explicit

    Sub StringTest()

    Dim MyTimer As Double
    Dim i As Long
    Dim j As Long
    Dim UpperLimit As Long
    Dim LoopTimes As Long
    Dim MyString As String
    Dim ResultColumn As Long

    Application.ScreenUpdating = False

    UpperLimit = 101
    LoopTimes = 10000000
    ResultColumn = 0

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Empty v. Empty"
    For j = 2 To UpperLimit
    MyString = ""
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If MyString = "" Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Empty v. Len(0)"
    For j = 2 To UpperLimit
    MyString = ""
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If Len(MyString) = 0 Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Empty v. Null"
    For j = 2 To UpperLimit
    MyString = ""
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If MyString = vbNullString Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Null v. Empty"
    For j = 2 To UpperLimit
    MyString = vbNullString
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If MyString = "" Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Null v. Len(0)"
    For j = 2 To UpperLimit
    MyString = vbNullString
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If Len(MyString) = 0 Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Null v. Null"
    For j = 2 To UpperLimit
    MyString = vbNullString
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If MyString = vbNullString Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Full v. Empty"
    For j = 2 To UpperLimit
    MyString = "Any Old Data"
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If MyString <> "" Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Full v. Len(0)"
    For j = 2 To UpperLimit
    MyString = "Any Old Data"
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If Len(MyString) <> 0 Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    ResultColumn = ResultColumn + 1
    Cells(1, ResultColumn).Value = "Full v. Null"
    For j = 2 To UpperLimit
    MyString = "Any Old Data"
    i = 0
    MyTimer = Timer
    While i < LoopTimes
    If MyString <> vbNullString Then
    i = i + 1
    End If
    Wend
    Cells(j, ResultColumn).Value = Timer - MyTimer
    Next j

    For j = 1 To ResultColumn
    Cells(UpperLimit + 2, j).Value = "=Average(R2C:R[-2]C)"
    Next
    Range("1:" & ResultColumn).EntireColumn.AutoFit

    Application.ScreenUpdating = True

    End Sub[/VBA]
    and the results were:

    Empty v. Empty 3.35204 secs (average)
    Empty v. Len(0) 3.07079 secs
    Empty v. Null 3.53828 secs
    Null v. Empty 3.38719 secs
    Null v. Len(0) 3.10751 secs
    Null v. Null 3.51327 secs
    Full v. Empty 3.34125 secs
    Full v. Len(0) 3.08453 secs
    Full v. Null 3.49797 secs
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  6. #26
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by TonyJollans
    Empty v. Empty 3.35204 secs (average)
    Empty v. Len(0) 3.07079 secs
    Empty v. Null 3.53828 secs
    Null v. Empty 3.38719 secs
    Null v. Len(0) 3.10751 secs
    Null v. Null 3.51327 secs
    Full v. Empty 3.34125 secs
    Full v. Len(0) 3.08453 secs
    Full v. Null 3.49797 secs
    Good Research !
    But does anyone from Microsoft had to say on this?

    How does the stat above showed the faster one?
    eg.
    Empty v. Len(0) 3.07079 secs - Who is Faster?

  7. #27
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    Hi sheeeng,

    Sorry - my wording is not clear. What that means is that the figure of 3.07079 seconds is the average time taken (out of 100 runs) for 1 million comparisons of the length of an empty string against 0.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  8. #28

  9. #29
    Administrator
    VP-Knowledge Base
    VBAX Guru MOS MASTER's Avatar
    Joined
    Apr 2005
    Location
    Breda, The Netherlands
    Posts
    3,281
    Location
    _________
    Groetjes,

    Joost Verdaasdonk
    M.O.S. Master

    Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
    (I don't answer questions asked through E-mail or PM's)

  10. #30
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by TonyJollans
    Hi sheeeng,

    Sorry - my wording is not clear. What that means is that the figure of 3.07079 seconds is the average time taken (out of 100 runs) for 1 million comparisons of the length of an empty string against 0.
    Never mind.

  11. #31
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location

  12. #32
    VBAX Mentor ALe's Avatar
    Joined
    Aug 2005
    Location
    Milan
    Posts
    383
    Location
    On my Pc (Excel 2002, 512 MBRam,WinXp Pro 2.8 GHz) time is the same (0.51243 vs 0.498412)

  13. #33
    VBAX Expert
    Joined
    Feb 2005
    Posts
    929
    Location
    I just stumbled across this and decided to compare Jake's "million" example (after fixing a few code typos) with "quick and dirty" stuff I have run (and found virtually no difference among the methods).

    On a Dell9100 running 3GHz dual core and 2GzRAM (+ ~ 50 background processes):
    Double Quotes: 0.327
    vbNullString: 0.327
    Len: 0.329

    not much difference
    "It's not just the due date that's important, it's also the do date" [MWE]

    When your problem has been resolved, mark the thread SOLVED by clicking on the Thread Tools dropdown menu at the top of the thread.

  14. #34
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Quote Originally Posted by MWE
    I just stumbled across this and decided to compare Jake's "million" example (after fixing a few code typos) with "quick and dirty" stuff I have run (and found virtually no difference among the methods).

    On a Dell9100 running 3GHz dual core and 2GzRAM (+ ~ 50 background processes):
    Double Quotes: 0.327
    vbNullString: 0.327
    Len: 0.329

    not much difference
    Thanks for the time, MWE. What I notice that you are using a pretty good computer. Haha! I wonder how can I have such a good hardware nowaday where the oil prices are spiking!

  15. #35
    VBAX Newbie
    Joined
    Sep 2005
    Posts
    2
    Location
    Looking at the article there is also a section on 'initializing' your strings with vbnullstring rather than "".

    On it's own, it does look like a good idea... vbnullstring was about 400% faster! BUT [and a big one], If you go and then reassign that string, the one initialized with "" did the reassign much faster. Also- a string that was alreay assigned a value, setting it to "" was also faster than setting it to vbnullstring.

    Bottom line, initializing the string to "" or vbnullstring and then assigning a value turn out to be just about identical, so it only makes sense to change code if you have alot of strings that you want to be null or empty and don't plan on assigning it a value...

    --

    I guess you have to be very careful who you trust when lookign for optimization advice

  16. #36
    Administrator
    2nd VP-Knowledge Base
    VBAX Master malik641's Avatar
    Joined
    Jul 2005
    Location
    Florida baby!
    Posts
    1,533
    Location
    Very interesting post Two thumbs up!

    I used DRJ's code (with 1 million iterations) for my test. I tried Tony's and I'm not sure if Excel crashed on me or the code takes longer than 15 mins to run , but I wasn't about to try that again.

    Intel Pentium 4
    2.80 GHz Processor
    778 MB RAM

    (Avg Secs)
    Double Quotes: 0.400507538
    vbNullString: 0.400899497
    Len: 0.404778894

    1/10,000 of a difference between Double Quotes and vbNullString...hmmh

    Made an interesting graph too. I thought I could make a mathematical analysis of the behavior, but I really can't. It looks very close to a Sine/Cosine wave (except Len, which pretty much stayed consistent)...I was hoping to find a logarithmic function ....this could have made a decent conclusion to what's the fastest (which would have been finding out what has the lowest limit).

    Check it out:




    New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.

  17. #37
    Moderator VBAX Mentor sheeeng's Avatar
    Joined
    May 2005
    Location
    Kuala Lumpur
    Posts
    392
    Location
    Thanks, Joseph. Nice graph there!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •