[AccessD] Data Types (was - Global Variable)

Brett Barabash BBarabash at TappeConstruction.com
Wed May 18 13:00:01 CDT 2005


Man, I spend all this time crafting a similar test and JC prepackages it
for me...

Remember some of the old arguments on this list about improving
performance?  Almost every time, the performance difference was
negligible (i.e. a human being couldn't tell the difference).

Not that long integers are a bad thing.  I use them all the time, even
if I think an integer will suffice.  When our shrink-wrap Access
accounting system GPF'ed when it reached check # 32768, it caused me to
vow never to allow this to happen in my code.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John W. Colby
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 arra
 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 TROUBLESHO
tion
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


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 lclsti
    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 
s 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 

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 Bobby Heid
Sent: Wednesday, May 18, 2005 11:51 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Data Types (was - Global Variable)


I always use longs because they are the 32-bit words.  Which is the
native word size on 32-bit architectures.  There are benefits (speed
mainly) to using 32-bit over 16-bit integers for calculations.

Bobby

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John Bartow
Sent: Wednesday, May 18, 2005 11:38 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Data Types (was - Global Variable)


I've considered no longer using Integers for variables because of the 16
bit issue. 

However, I have been reluctant to do so for field datatypes. When
utilizing data from another non-access application this may affect how
the other apps behave. Comments?

John


--------------------------------------------------------------------------------------------------------------------
The information in this email may contain confidential information that 
is legally privileged. The information is only for the use of the intended 
recipient(s) named above. If you are not the intended recipient(s), you 
are hereby notifie
aking 
of any action in regard to the content of this email is strictly prohibited.  If 
transmission is incorrect, unclear, or incomplete, please notify the sender 
immediately. The authorized recipient(s) of this information is/are prohibited 
from disclosing this information to any other party and is/are required to 
destroy the information after its stated need has been fulfilled.

Any views expressed in this message are those of the individual
sender, except where the sender specifies and with authority,
states them to be the views of Tappe Construction Co.

This footer also confirms that this email message has been scanned
for the presence of computer viruses.Scanning of this message and
addition of this footer is performed by SurfControl E-mail Filter software
in conjunction with virus detection software.




More information about the AccessD mailing list