Results 1 to 14 of 14
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    In case anybody's interested, I did a small timing test among VB. Net, VB 6 and VBA 6.3 (in Word 2002).

    All I was trying to do is compare the relative speeds of using 3 alternatives for doing the same thing, i.e., whether it is more efficient to use False, vbFalse or vbTristate.vbFalse. I did not totally optimize the code in each case, but the VB .Net code is mucho faster.

    ' VBA
    'TimeFalse: 16844 milliseconds
    'TimevbFalse: 16855 milliseconds
    'TimeTriFalse: 19668 milliseconds
    'TimeFalse1: 45445 milliseconds
    'TimevbFalse1: 35471 milliseconds
    'TimeTriFalse1: 38796 milliseconds

    'VB 6
    ' TimeFalse: 16343 milliseconds
    ' TimevbFalse: 16334 milliseconds
    ' TimeTriFalse: 17104 milliseconds
    ' TimeFalse1: 43152 milliseconds
    ' TimevbFalse1: 33949 milliseconds
    ' TimeTriFalse1: 37514 milliseconds


    'VB .Net
    'TimeFalse: 1031 milliseconds
    'TimevbFalse: 1552 milliseconds
    'TimeTriFalse: 1553 milliseconds
    'TimeFalse1: 1542 milliseconds
    'TimevbFalse1: 1782 milliseconds
    'TimeTriFalse1: 1793 milliseconds

  2. #2
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    Always interested in speed...

    but the question now is, what do i do with an Access project to get the vb.net speed?

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    Wait for the next version of Office? <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    Charlotte

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    I find it hard to believe that VB .Net runs in 1 second something that takes 16 seconds to run in VB. I've heard of optimizations, but that's incredible. Perhaps these little tweaks account for the massive increase in the size of the runtime...

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    You can use VB.Net to use, say, Access/DAO/ADO via Automation.

    The improved speed of VB.Net may more than compensate for the added overhead of Automation.

  6. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    VBA code is very sensitive to small coding changes.
    I've seen improvements with VBA of over 16000% with very small coding changes.

    Each of the tests went thru a loop 100 million times and used API calls for accurate timing., so it ain't no fluke.

    Remember, this was a test of using False vs. vbFalse vs. Vbtristate.vbfalse, pretty simple stuff.

    I list below all 3 modules used.

    The following is the VBA code:
    Option Explicit
    Private Declare Function GetTickCount Lib "Kernel32" () As Long
    Public Sub main()
    Dim lngTime As Long
    ' VBA
    'TimeFalse: 16844 milliseconds
    'TimevbFalse: 16855 milliseconds
    'TimeTriFalse: 19668 milliseconds
    'TimeFalse1: 45445 milliseconds
    'TimevbFalse1: 35471 milliseconds
    'TimeTriFalse1: 38796 milliseconds

    'VB 6
    ' TimeFalse: 16343 milliseconds
    ' TimevbFalse: 16334 milliseconds
    ' TimeTriFalse: 17104 milliseconds
    ' TimeFalse1: 43152 milliseconds
    ' TimevbFalse1: 33949 milliseconds
    ' TimeTriFalse1: 37514 milliseconds


    'VB .Net
    'TimeFalse: 1031 milliseconds
    'TimevbFalse: 1552 milliseconds
    'TimeTriFalse: 1553 milliseconds
    'TimeFalse1: 1542 milliseconds
    'TimevbFalse1: 1782 milliseconds
    'TimeTriFalse1: 1793 milliseconds


    lngTime = GetTickCount()
    TimeFalse
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeFalse: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimevbFalse
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimevbFalse: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeTriFalse
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeTriFalse: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeFalse1
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeFalse1: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimevbFalse1
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimevbFalse1: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeTriFalse1
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeTriFalse1: " & Format(lngTime) & " milliseconds")
    End Sub
    Public Sub TimeFalse()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    blnX = False
    Next i
    End Sub
    Public Sub TimevbFalse()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    blnX = vbFalse
    Next i
    End Sub
    Public Sub TimeTriFalse()
    Dim blnX As VbTriState
    Dim i As Long
    For i = 1 To 100000000
    blnX = VbTriState.vbFalse
    Next i
    End Sub
    Public Sub TimeFalse1()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    If blnX = False Then
    blnX = False
    End If
    Next i
    End Sub
    Public Sub TimevbFalse1()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    If blnX = vbFalse Then
    blnX = vbFalse
    End If
    Next i
    End Sub
    Public Sub TimeTriFalse1()
    Dim blnX As VbTriState
    Dim i As Long
    For i = 1 To 100000000
    If blnX = VbTriState.vbFalse Then
    blnX = VbTriState.vbFalse
    End If
    Next i
    End Sub

    The following is the VB code:
    Option Explicit
    Private Declare Function GetTickCount Lib "Kernel32" () As Long
    Public Sub main()
    Dim lngTime As Long
    ' VBA
    'TimeFalse: 16864 milliseconds
    'TimevbFalse: 16854 milliseconds
    'TimeFalse1: 42381 milliseconds
    'TimevbFalse1: 35491 milliseconds

    'VB 6
    ' TimeFalse: 16343 milliseconds
    ' TimevbFalse: 16334 milliseconds
    ' TimeTriFalse: 17104 milliseconds
    ' TimeFalse1: 43152 milliseconds
    ' TimevbFalse1: 33949 milliseconds
    ' TimeTriFalse1: 37514 milliseconds


    'VB .Net
    'TimeFalse: 1031 milliseconds
    'TimevbFalse: 1552 milliseconds
    'TimeTriFalse: 1553 milliseconds
    'TimeFalse1: 1542 milliseconds
    'TimevbFalse1: 1782 milliseconds
    'TimeTriFalse1: 1793 milliseconds


    lngTime = GetTickCount()
    TimeFalse
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeFalse: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimevbFalse
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimevbFalse: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeTriFalse
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeTriFalse: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeFalse1
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeFalse1: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimevbFalse1
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimevbFalse1: " & Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeTriFalse1
    lngTime = GetTickCount() - lngTime
    Debug.Print ("TimeTriFalse1: " & Format(lngTime) & " milliseconds")
    End Sub
    Public Sub TimeFalse()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    blnX = False
    Next i
    End Sub
    Public Sub TimevbFalse()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    blnX = vbFalse
    Next i
    End Sub
    Public Sub TimeTriFalse()
    Dim blnX As VbTriState
    Dim i As Long
    For i = 1 To 100000000
    blnX = VbTriState.vbFalse
    Next i
    End Sub
    Public Sub TimeFalse1()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    If blnX = False Then
    blnX = False
    End If
    Next i
    End Sub
    Public Sub TimevbFalse1()
    Dim blnX As Boolean
    Dim i As Long
    For i = 1 To 100000000
    If blnX = vbFalse Then
    blnX = vbFalse
    End If
    Next i
    End Sub
    Public Sub TimeTriFalse1()
    Dim blnX As VbTriState
    Dim i As Long
    For i = 1 To 100000000
    If blnX = VbTriState.vbFalse Then
    blnX = VbTriState.vbFalse
    End If
    Next i
    End Sub

    The following is the VB .Net code:

    Option Strict Off
    Option Explicit On
    Module modTestTime
    Private Declare Function GetTickCount Lib "Kernel32" () As Integer
    Public Sub main()
    Dim lngTime As Integer
    ' VBA
    'TimeFalse: 16864 milliseconds
    'TimevbFalse: 16854 milliseconds
    'TimeFalse1: 42381 milliseconds
    'TimevbFalse1: 35491 milliseconds

    'VB 6
    ' TimeFalse: 16343 milliseconds
    ' TimevbFalse: 16334 milliseconds
    ' TimeTriFalse: 17104 milliseconds
    ' TimeFalse1: 43152 milliseconds
    ' TimevbFalse1: 33949 milliseconds
    ' TimeTriFalse1: 37514 milliseconds


    'VB .Net
    'TimeFalse: 1032 milliseconds
    'TimevbFalse: 1022 milliseconds
    'TimeTriFalse: 1021 milliseconds
    'TimeFalse1: 1172 milliseconds
    'TimevbFalse1: 1532 milliseconds
    'TimeTriFalse1: 1543 milliseconds


    lngTime = GetTickCount()
    TimeFalse()
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.WriteLine("TimeFalse: " & VB6.Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimevbFalse()
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.WriteLine("TimevbFalse: " & VB6.Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeTriFalse()
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.WriteLine("TimeTriFalse: " & VB6.Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeFalse1()
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.WriteLine("TimeFalse1: " & VB6.Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimevbFalse1()
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.WriteLine("TimevbFalse1: " & VB6.Format(lngTime) & " milliseconds")
    lngTime = GetTickCount()
    TimeTriFalse1()
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.WriteLine("TimeTriFalse1: " & VB6.Format(lngTime) & " milliseconds")
    End Sub
    Public Sub TimeFalse()
    Dim blnX As Boolean
    Dim i As Integer
    For i = 1 To 100000000
    blnX = False
    Next i
    End Sub
    Public Sub TimevbFalse()
    Dim blnX As Boolean
    Dim i As Integer
    For i = 1 To 100000000
    blnX = vbFalse
    Next i
    End Sub
    Public Sub TimeTriFalse()
    Dim blnX As TriState
    Dim i As Integer
    For i = 1 To 100000000
    blnX = TriState.False
    Next i
    End Sub
    Public Sub TimeFalse1()
    Dim blnX As Boolean
    Dim i As Integer
    For i = 1 To 100000000
    If blnX = False Then
    blnX = False
    End If
    Next i
    End Sub
    Public Sub TimevbFalse1()
    Dim blnX As Boolean
    Dim i As Integer
    For i = 1 To 100000000
    If blnX = vbFalse Then
    blnX = vbFalse
    End If
    Next i
    End Sub
    Public Sub TimeTriFalse1()
    Dim blnX As TriState
    Dim i As Integer
    For i = 1 To 100000000
    If blnX = TriState.False Then
    blnX = TriState.False
    End If
    Next i
    End Sub
    End Module

  7. #7
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    I'll try the code myself, but I wonder: How come you use GetTickCount() rather than timeGetTime()? Is one timing routing more accurate than the other? or dont they matter in this case?

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    I wonder whether VB .NET is really looping 100 million times, or whether it realizes that the loop does the same thing every time and just does it once. <img src=/S/wink.gif border=0 alt=wink width=15 height=15> Still, that would be a second well spent.

    In VB .NET, when you use

    Dim blnX as Boolean

    does blnX have an initial value of False? I'm assuming not, since there is virtually no overhead for the extra If...Then... code in your test. If not False, though, what would its initial value be??

  9. #9
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    As jscher2000 points out, the .net compiler may have hoisted the loop contents.

    I would like to see how all compilers fare with a construct of the form
    <pre>dim b as boolean
    for i = 1 to N
    b = f(i)
    next i
    '...
    function f(i as long) as boolean
    f = true
    end function
    </pre>


    because it is rare that i would assign a constant boolean value 1e6 times as a 'benchmark'

  10. #10
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    Another interesting, pedantic but interesting, variant would be to use the GetProcessTimes API call instead of GetTickCount to find out how much of the time is spent in the Kernel and how much in the program. With this kind of benchmark there should be very little Kernel time but we have to remember this is Microsoft.

    Regards,

    Kevin Bell

  11. #11
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    Perhaps, but I was interested only in testing the assignment.

    I intentionally allowed for the possibility that VB .Net might be smart enough to optimize the code/execution.

  12. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    Here's a better set of numbers:

    ' VBA
    ' TimeFalse: 73806 milliseconds
    ' TimevbFalse: 73796 milliseconds
    ' TimeTriFalse: 77371 milliseconds
    'VB 6
    ' TimeFalse: 70221 milliseconds
    ' TimevbFalse: 70201 milliseconds
    ' TimeTriFalse: 75318 milliseconds
    'VB .Net
    ' TimeFalse: 3726 milliseconds
    ' TimevbFalse: 2935 milliseconds
    ' TimeTriFalse: 3715 milliseconds

    THe following was the VBA and VB code:

    Option Explicit
    Private Declare Function GetTickCount Lib "Kernel32" () As Long
    Public Sub Main()
    Dim lng0 As Long
    Dim lng1 As Long
    Dim lngTime As Long
    lngTime = GetTickCount()
    TimeFalse lng0, lng1
    lngTime = GetTickCount() - lngTime
    Debug.Print "TimeFalse: ";
    Debug.Print Format(lngTime) & " milliseconds" & ": " & lng0 & ", " & lng1
    lngTime = GetTickCount()
    TimevbFalse lng0, lng1
    lngTime = GetTickCount() - lngTime
    Debug.Print "TimevbFalse: ";
    Debug.Print Format(lngTime) & " milliseconds" & ": " & lng0 & ", " & lng1
    lngTime = GetTickCount()
    TimeTriFalse lng0, lng1
    lngTime = GetTickCount() - lngTime
    Debug.Print "TimeTriFalse: ";
    Debug.Print Format(lngTime) & " milliseconds" & ": " & lng0 & ", " & lng1
    End Sub
    Public Sub TimeFalse(lng0 As Long, lng1 As Long)
    Dim blnX As Boolean
    Dim i As Long
    lng0 = 0
    lng1 = 0
    For i = 1 To 100000000
    If i Mod 2 = 0 Then
    blnX = True
    lng0 = lng0 + 1
    Else
    blnX = False
    lng1 = lng1 + 1
    End If
    Next i
    End Sub
    Public Sub TimevbFalse(lng0 As Long, lng1 As Long)
    Dim blnX As Boolean
    Dim i As Long
    lng0 = 0
    lng1 = 0
    For i = 1 To 100000000
    If i Mod 2 = 0 Then
    blnX = vbTrue
    lng0 = lng0 + 1
    Else
    blnX = vbFalse
    lng1 = lng1 + 1
    End If
    Next i
    End Sub
    Public Sub TimeTriFalse(lng0 As Long, lng1 As Long)
    Dim blnX As VbTriState
    Dim i As Long
    lng0 = 0
    lng1 = 0
    For i = 1 To 100000000
    If i Mod 2 = 0 Then
    blnX = VbTriState.vbFalse
    lng0 = lng0 + 1
    Else
    blnX = VbTriState.vbFalse
    lng1 = lng1 + 1
    End If
    Next i
    End Sub

    the following was the VB .Net code:

    Option Strict Off
    Option Explicit On
    Module modTestRandomStuff
    Private Declare Function GetTickCount Lib "Kernel32" () As Integer
    Public Sub Main()
    Dim lng0 As Integer
    Dim lng1 As Integer
    Dim lngTime As Integer
    lngTime = GetTickCount()
    TimeFalse(lng0, lng1)
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.Write("TimeFalse: ")
    System.Diagnostics.Debug.WriteLine(VB6.Format(lngT ime) & " milliseconds" & ": " & lng0 & ", " & lng1)
    lngTime = GetTickCount()
    TimevbFalse(lng0, lng1)
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.Write("TimevbFalse: ")
    System.Diagnostics.Debug.WriteLine(VB6.Format(lngT ime) & " milliseconds" & ": " & lng0 & ", " & lng1)
    lngTime = GetTickCount()
    TimeTriFalse(lng0, lng1)
    lngTime = GetTickCount() - lngTime
    System.Diagnostics.Debug.Write("TimeTriFalse: ")
    System.Diagnostics.Debug.WriteLine(VB6.Format(lngT ime) & " milliseconds" & ": " & lng0 & ", " & lng1)
    End Sub
    Public Sub TimeFalse(ByRef lng0 As Integer, ByRef lng1 As Integer)
    Dim blnX As Boolean
    Dim i As Integer
    lng0 = 0
    lng1 = 0
    For i = 1 To 100000000
    If i Mod 2 = 0 Then
    blnX = True
    lng0 = lng0 + 1
    Else
    blnX = False
    lng1 = lng1 + 1
    End If
    Next i
    End Sub
    Public Sub TimevbFalse(ByRef lng0 As Integer, ByRef lng1 As Integer)
    Dim blnX As Boolean
    Dim i As Integer
    lng0 = 0
    lng1 = 0
    For i = 1 To 100000000
    If i Mod 2 = 0 Then
    blnX = TriState.True
    lng0 = lng0 + 1
    Else
    blnX = TriState.False
    lng1 = lng1 + 1
    End If
    Next i
    End Sub
    Public Sub TimeTriFalse(ByRef lng0 As Integer, ByRef lng1 As Integer)
    Dim blnX As TriState
    Dim i As Integer
    lng0 = 0
    lng1 = 0
    For i = 1 To 100000000
    If i Mod 2 = 0 Then
    blnX = TriState.False
    lng0 = lng0 + 1
    Else
    blnX = TriState.False
    lng1 = lng1 + 1
    End If
    Next i
    End Sub
    End Module

  13. #13
    Gold Lounger
    Join Date
    Dec 2000
    Location
    New Hampshire, USA
    Posts
    3,386
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    Cluck! Cluck!

    You just heard the sound of the chickens I counted before they hatched!

    Sigh! I found a case in which VB .Net is slower than either VBA or VB 6. However, this could be due to my PC being only a Pentium II 400, which is less than the recommended system requirements for VS .Net.

    The following results were obtained using

    If Int(2 * Rnd()) Mod 2 = 0 Then

    instead of

    If i Mod 2 = 0 Then

    That's sure a lot of overhead for calling a built-in simple function.


    ' VBA
    ' TimeFalse: 144428 milliseconds
    ' TimevbFalse: 145489 milliseconds
    ' TimeTriFalse: 139731 milliseconds
    'VB 6
    ' TimeFalse: 143527 milliseconds
    ' TimevbFalse: 144658 milliseconds
    ' TimeTriFalse: 148984 milliseconds
    'VB .Net
    ' TimeFalse: 241417 milliseconds
    ' TimevbFalse: 247216 milliseconds
    ' TimeTriFalse: 246955 milliseconds

  14. #14
    4 Star Lounger
    Join Date
    Dec 2000
    Location
    Faifax, Virginia, USA
    Posts
    542
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB.Net vs. VB 6 vs VBA 6.3 ((Office XP))

    <img src=/S/smile.gif border=0 alt=smile width=15 height=15> they are pesky critters

Posting Permissions

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