[AccessD] Number vs text data type

If you must bring in the speed issue, the amount of time spent sorting is 
not as significant as the time taken to bring the data to the processor.  A 
2 or 3 gigahertz processor can sort an amazing number of records in a tiny 
fraction of a second, far faster than the data can be requested and 
transferred from a hard drive.  When the data is requested over a 100 
megabit LAN, the lenght of time is significantly magnified.  Add some 
traffic to the LAN and everyone using the LAN is delayed.  Now pull the data 
over a slower connection and the difference in size between numeric and 
string data types becomes more significant.  Use a slower connection and the 
time taken to transfer the data is even greater.

Some people don't realize that the number 933456123 is stored in 4 bytes as 
a long yet takes 9 bytes as a string and 18 bytes as unicode.  The string 
will take more than twice as long to retrieve given a large recordset where 
disk latency isn't the primary factor.  Twice as long for a three second 
retreival time is an extra 3 seconds.  (100 mbit LAN, 9 bits/byte avg 
throughput, 50% share of bandwidth = 5.5 million bytes per second ~ 1.4 
million long fields = ~ 700 thousand string fields ~ two billion processor 
cycles allowing about 700 processor cycles per record for sorting the number 
and 1.4 million for sorting the string)  The same size of recordset sorting 
time, even if it takes four times as long to sort a string over a numeric, 
will likely yeild a difference of .1 vs .4 seconds, a delay of 3 tenths of a 
second depending on the algorithm.  Although it only takes twice as long to 
retrieve, this makes a bigger difference than the fact that it takes perhaps 
four times as long to sort.  Another significant factor is that the index 
size of a numeric is smaller than that of a string resulting in faster LAN 
performance for any searches on the field with a resulting benefit to all 
users of that LAN.

In this day of data over distance, size still matters, even if the distance 
is only to a local hard drive.  Put that drive on a LAN or WAN or over the 
internet and it matters even more.  As processors seem to increase in speed 
at a greater rate than bandwidth does (if you discount 300 baud modems or 
audio cassette tape drives vs hard disks and ponder how long we've had 56K 
modems and 100mbit LANS), the advantage of smaller datatypes has only been 
increasing with the passage of time.

Beware the situation where a user may want to append an 'A' or a 'B' to a 
field that was spec'ed only to contain whole contain numbers, or those who 
might one day need a 'none' or 'all' selection in the table rather than 
having it unioined into a recordset.  Only when you have the absolute 
ability to constrain the data ('data' does not include formatting hyphens or 
spaces or other fixed characters) to numerical digits can you take advantage 
of the size differences between numeric and text datatypes.  On the scale 
most Access databased operate this may not seem very important but there is 
still some advantage to be gained.

Hen

_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online  
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963