[AccessD] Data Types (was - Global Variable)

John W. Colby jwcolby at colbyconsulting.com
Wed May 18 14:50:49 CDT 2005


Yes, so I see.  That is an unfortunate side effect of cut and paste, it does
leave one open to not changing everything that needs changing.  I assume
that you looked no further?  So...

That is ONE test.  I am not claiming superiority, however I DID bother to
perform tests (long ago) and found none of what you are claiming to be true.
Stop, slow down, pull your foot out of your mouth and answer how operations
other than long can POSSIBLY be going through "pack/unpack" operations and
be so much slower.  It isn't born out in actual testing.  Because of course,
it just isn't the case.

Here is the adjusted code (for the one that was wrong):

Function TestLongAdd()
On Error GoTo Err_TestLongAdd
Dim lngCnt As Long
Dim lngAdd As Long
Const clngMaxCnt As Long = 255
    For lngCnt = 1 To clngMaxCnt
        lngAdd = lngAdd + 1
    Next lngCnt
Exit_TestLongAdd:
On Error Resume Next
Exit Function
Err_TestLongAdd:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestLongAdd"
        Resume Exit_TestLongAdd
    Resume 0    '.FOR TROUBLESHOOTING
End Function

And the adjusted results.

SpeedTest
Time Adds
 1443 
 1469 
 2725 
Time Counts
 4976 
 4188 
 5236 
Time Compares
 1553 
 1492 
 1489 
Time Parameters
 2750 
 2682 
 2676 

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Wednesday, May 18, 2005 3:27 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Data Types (was - Global Variable)


JC, stop.  Breath.  Okay, no RE-READ my post!  You have a function, called
TestLongAdd.  The purpose, I can only assume, is to test the addition of
long integers.  HOWEVER (VERY VERY VERY BIG HOWEVER), you dimension lngAdd
as INTEGER!!! INTEGER INTEGER INTEGER!!!!  You are now testing the addition
of a 16 bit Integer.... not a 32 bit LONG Integer.

Please JC, if you are going to claim superiority, please read the posts
arguing on the otherside.  It gets annoying after a while, with you
wandering about the list going 'I'm JC, so I must be right!'.  <grin> (okay,
that might have been below the belt..but it was avoidable if you would
actually read something, instead of outright banishing every thought that
disagrees with your opinion).

Just an FYI, I must concede, after my own testing...that run in an
interpreted state, there is little difference between an integer and a
long...so in an Access .mdb, I can see that the only true advantage would be
portability later, into VB.

Drew

-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Wednesday, May 18, 2005 2:10 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Data Types (was - Global Variable)


Drew, I wrote that code in the time it took to reply to the email.  I
intentionally used longs for every piece that was not specific to what I
wanted to test.  The difference between you and I is that I thought about it
first and then did it.  You don't bother to think.  The usage of longs in no
way invalidates the test results and allows me to easily bump up the count
sizes if I desired for some reason.  The usage of longs in counters that are
going to test things that we know are going to take very short times
individually are exactly where longs are useful, as opposed to your
"everywhere"

I understand you are frustrated at the moment.  Focus on learning about what
you are speaking with such supposed authority.  Had you bothered to run your
own tests you wouldn't be chewing on your shoe.

John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of DWUTKA at marlow.com
Sent: Wednesday, May 18, 2005 2:50 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] Data Types (was - Global Variable)


ROTFLMAO!  JC, next time you are trying to prove a point, about practices,
and stuff like that, check your code! LOL

Function TestLongAdd()
On Error GoTo Err_TestLongAdd
Dim lngCnt As Long
Dim lngAdd As Integer
Const clngMaxCnt As Long = 255
    For lngCnt = 1 To clngMaxCnt
        lngAdd = lngAdd + 1
    Next lngCnt
Exit_TestLongAdd:
On Error Resume Next
Exit Function
Err_TestLongAdd:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestLongAdd"
        Resume Exit_TestLongAdd
    Resume 0    '.FOR TROUBLESHOOTING
End Function

Notice the variable..... lngAdd As Integer?  I know you prefixed it with
lng...but you did declare it as an Integer.  Not sure where you learned to
code, but it's the part after the AS that counts.... <grin>

So, I did my own test.  I put this in VB 6

Option Explicit
Private Declare Function GetTickCount Lib "kernel32" () As Long Private Sub
Command1_Click() Dim lngNow As Long Dim i As Long lngNow = GetTickCount For
i = 1 To 100
    TestIntCount
Next i
Me.txtInteger = GetTickCount - lngNow
End Sub
Private Sub Command2_Click()
Dim lngNow As Long
Dim i As Long
lngNow = GetTickCount
For i = 1 To 100
    TestLongCount
Next i
Me.txtLong = GetTickCount - lngNow
End Sub
Private Sub TestLongCount()
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
i = -32000
j = -32000
k = -32000
l = -32000
Do Until l = 32000
    l = l + 1
    Do Until k = 32000
        k = k + 1
        Do Until i = 32000
            i = i + 1
            Do Until j = 32000
                j = j + 1
            Loop
        Loop
    Loop
Loop
End Sub
Private Sub TestLongNext()
Dim i As Long
Dim j As Long
For i = -32000 To 32000
    For j = -32000 To 32000
    Next j
Next i
End Sub
Private Sub TestIntNext()
Dim i As Integer
Dim j As Integer
For i = -32000 To 32000
    For j = -32000 To 32000
    Next j
Next i
End Sub
Private Sub TestIntCount()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
i = -32000
j = -32000
k = -32000
l = -32000
Do Until l = 32000
    l = l + 1
    Do Until k = 32000
        k = k + 1
        Do Until i = 32000
            i = i + 1
            Do Until j = 32000
                j = j + 1
            Loop
        Loop
    Loop
Loop
End Sub

Private Sub Command3_Click()
Dim lngNow As Long
Dim i As Long
lngNow = GetTickCount
For i = 1 To 1
    TestLongNext
Next i
Me.txtLong = GetTickCount - lngNow
End Sub

Private Sub Command4_Click()
Dim lngNow As Long
Dim i As Integer
lngNow = GetTickCount
For i = 1 To 1
    TestIntNext
Next i
Me.txtInteger = GetTickCount - lngNow
End Sub

Speeds are close (longs still quicker, but not by much...and actually, not
consistantly either) when run in debug mode.  When compiled, however, here
are my test results (ran 2 or 3 times, all numbers were almost identical to
initial run. (This is on an Intel PIII 1ghz.)

Adding: Integer 490 ticks vs. Long Integer 121 ticks

NextLoops: Integer 66886 ticks vs. Long Integer 13920

I did use a long integer in the for next in the add cycle, but not in the
for next cycle.  Other then that, I would say this is a direct test of
speed.  Pasted this in VB JC.... results are flaky when interpretted.

Drew

-----Original Message-----
From: John W. Colby [mailto:jwcolby at colbyconsulting.com]
Sent: Wednesday, May 18, 2005 12:40 PM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Data Types (was - Global Variable)


All data types have a use and "accepted practice" (except by Drew of course)
is to use the data type that will hold the required data and no larger.
There are of course reasons for that, but some developers take the
simplistic view that "the register is 32 bits so I should just always use
that".  Nonsense.  

Every X86 processor has instructions for all the operations for 16 bit, 32
bit and now, 64 bit operations.  It is simply NOT TRUE that using a long
integer is faster than using an integer.  THAT is simply a myth.  The X86
instruction set is already a mish mash of instruction lengths simply because
it has to have backwards compatibility clear back to the olden days.  The
instructions are all there, they can be used, a 16 bit number will be placed
in a "16 bit register".  It will use 16 bits of data cache.  There is
another 16 bit register (the other half of the 32 bit register) that MAY be
made available for some other use.  You don't know that it will, but
likewise you don't know that it won't.  There are machine instructions for
accessing 8 bit "chunks", 16 bit chunks and 32 bit chunks of a register.

In fact it is entirely likely that something like an array of 16 bit numbers
will be packed 2 numbers per physical memory location and thus reading and
writing the array from memory to cache may be faster than reading and
writing the same array using 32 bit numbers where only 16 bits are really
needed.  

In the end, all that level of stuff is handled by machine code.  The VB
required to deal with it is created by an interpreter, which is in turn
created by a compiler.  That compiler has optimizations which will do it's
best to pack memory into words etc.  

Using a long integer absolutely makes sense in things like PKs of a table,
where the data has to be compared against other FKs and the numbers of data
items can reach past the ability of a 16 bit number to deal with them.
However to say that it is necessarily faster for code and variables stored
in memory is simply not true.  Take an assembler course and you will quickly
learn about all the data sizes built right in to the registers and
instructions of the machine itself.  They are not "turned off" just because
the machine is a 32 bit machine.  And the compiler WILL EFFICIENTLY USE ALL
OF THEM (or try to), as will the VB interpreter.

On top of that are the very obvious examples such as using a long integer to
hold the number of family members for example.  Not only is it a total waste
of space and not any faster (and probably slower), but in certain instances
the fact that a huge number is trying to go into a variable of "the right
size" can cause an error and alert you to the fact that your logic is
faulty.

This would be a good place to do some timing tests.

Option Compare Database
Option Explicit

Function TestLongAdd()
On Error GoTo Err_TestLongAdd
Dim lngCnt As Long
Dim lngAdd As Integer
Const clngMaxCnt As Long = 255
    For lngCnt = 1 To clngMaxCnt
        lngAdd = lngAdd + 1
    Next lngCnt
Exit_TestLongAdd:
On Error Resume Next
Exit Function
Err_TestLongAdd:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestLongAdd"
        Resume Exit_TestLongAdd
    Resume 0    '.FOR TROUBLESHOOTING
End Function
Function TestIntAdd()
On Error GoTo Err_TestIntAdd
Dim lngCnt As Long
Dim intAdd As Integer
Const clngMaxCnt As Long = 255
    For lngCnt = 1 To clngMaxCnt
        intAdd = intAdd + 1
    Next lngCnt
Exit_TestIntAdd:
On Error Resume Next
Exit Function
Err_TestIntAdd:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestIntAdd"
        Resume Exit_TestIntAdd
    Resume 0    '.FOR TROUBLESHOOTING
End Function
Function TestByteAdd()
On Error GoTo Err_TestByteAdd
Dim lngCnt As Long
Dim bytAdd As Byte
Const clngMaxCnt As Long = 255
    For lngCnt = 1 To clngMaxCnt
        bytAdd = bytAdd + 1
    Next lngCnt
Exit_TestByteAdd:
On Error Resume Next
Exit Function
Err_TestByteAdd:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestByteAdd"
        Resume Exit_TestByteAdd
    Resume 0    '.FOR TROUBLESHOOTING
End Function
Function TestLongCnt()
On Error GoTo Err_TestLongCnt
Dim lngCnt As Long
Const clngMaxCnt As Long = 254
    For lngCnt = 1 To clngMaxCnt
    Next lngCnt
Exit_TestLongCnt:
Exit Function
Err_TestLongCnt:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestLongCnt"
        Resume Exit_TestLongCnt
    Resume 0    '.FOR TROUBLESHOOTING
End Function
Function TestIntCnt()
On Error GoTo Err_TestIntCnt
Dim intCnt As Integer
Const clngMaxCnt As Long = 254
    For intCnt = 1 To clngMaxCnt
    Next intCnt
Exit_TestIntCnt:
Exit Function
Err_TestIntCnt:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestIntCnt"
        Resume Exit_TestIntCnt
    Resume 0    '.FOR TROUBLESHOOTING
End Function
Function TestByteCnt()
On Error GoTo Err_TestByteCnt
Dim bytCnt As Byte
Const clngMaxCnt As Long = 254
    For bytCnt = 1 To clngMaxCnt
    Next bytCnt
Exit_TestByteCnt:
Exit Function
Err_TestByteCnt:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.TestByteCnt"
        Resume Exit_TestByteCnt
    Resume 0    '.FOR TROUBLESHOOTING
End Function

Function TestLongCompare()
Dim lngCnt As Long
Const clngMaxCnt As Long = 254
Const clngNum1 As Long = 1
Const clngNum2 As Long = 2

    For lngCnt = 1 To clngMaxCnt
        If clngNum1 > clngNum2 Then
        End If
    Next lngCnt
End Function
Function TestIntCompare()
Dim lngCnt As Long
Const clngMaxCnt As Long = 254
Const cintNum1 As Integer = 1
Const cintNum2 As Integer = 2

    For lngCnt = 1 To clngMaxCnt
        If cintNum1 > cintNum2 Then
        End If
    Next lngCnt
End Function
Function TestByteCompare()
Dim lngCnt As Long
Const clngMaxCnt As Long = 254
Const cbytNum1 As Byte = 1
Const cbytNum2 As Byte = 2

    For lngCnt = 1 To clngMaxCnt
        If cbytNum1 > cbytNum2 Then
        End If
    Next lngCnt
End Function
Function TestLongParam(lng As Long)

End Function
Function TestIntParam(lint As Integer)

End Function
Function TestBytParam(byt As Byte)

End Function
Function SpeedTest()
Dim lclstimer As clsTimer
Const clngMaxCnt As Long = 100000
Const clngMaxCnt2 As Long = 1000000
Const clngMaxCnt3 As Long = 10000000
Dim lngCnt As Long
Dim lngParam As Long
Dim intParam As Integer
Dim bytParam As Byte

    Debug.Print "Time Adds"
    Set lclstimer = New clsTimer
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt
        TestLongAdd
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt
        TestIntAdd
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt
        TestByteAdd
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    
    Debug.Print "Time Counts"
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt2
        TestLongCnt
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt2
        TestIntCnt
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt2
        TestByteCnt
    Next lngCnt
    Debug.Print lclstimer.EndTimer

    Debug.Print "Time Compares"
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt
        TestLongCompare
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt
        TestIntCompare
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt
        TestByteCompare
    Next lngCnt
    Debug.Print lclstimer.EndTimer

    Debug.Print "Time Parameters"
    lngParam = 1
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt3
        TestLongParam lngParam
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    intParam = 1
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt3
        TestIntParam intParam
    Next lngCnt
    Debug.Print lclstimer.EndTimer
    bytParam = 1
    lclstimer.StartTimer
    For lngCnt = 1 To clngMaxCnt3
        TestBytParam bytParam
    Next lngCnt
    Debug.Print lclstimer.EndTimer

Exit_SpeedTest:
On Error Resume Next
    Set lclstimer = Nothing
Exit Function
Err_SpeedTest:
        MsgBox Err.Description, , "Error in Function
zbasTestErrHndlr.SpeedTest"
        Resume Exit_SpeedTest
    Resume 0    '.FOR TROUBLESHOOTING
End Function

>From the debug window:

SpeedTest
Time Adds
 1459 
 1459 
 2700 
Time Counts
 4976 
 4148 
 5153 
Time Compares
 1523 
 1483 
 1507 
Time Parameters
 2763 
 2663 
 2711 

What this shows is that:

For adds, the integer is marginally faster than the long for adds, byt the
byts fares abysmally. For counts,the integer is significantly faster than
the long which is the same as the byte. For compares, the int is marginally
faster than the long which is marginally faster than the byte. For passing
parameters, long is the slowest, with integers being the fastest.

That my friends is all I am going to do for this particular discussion.  As
you can see, the answer isn't a simple "the long is always faster".  I did
my own tests for all kinds of stuff awhile back, including passing
parameters and the speed issue is NOT simple.  This does not even get into
memory use, hauling data off the hard disk into memory and from memory into
cache, and from cache into registers and then all the way back to the disk.

This is NOT addressed to Drew, since I already know what his response will
be.  The subject of data type is a complex one.  The simplestic "always use
32 bits" will work but is NOT best practice for a whole slew of reasons.  

And no Drew, I will NOT answer your "arguments".

John W. Colby
www.ColbyConsulting.com 





More information about the AccessD mailing list