View Single Post
  #6  
Old February 25th, 2010, 07:40 PM posted to microsoft.public.access.tablesdbdesign
David W. Fenton
external usenet poster
 
Posts: 3,373
Default Restated: "Fields are expensive, records are cheap"

"Allen Browne" wrote in
:

While there's some truth in that simple view, the reality is much
more complex than that. Access uses pointers to store the data. If
you define a Text field as 255 characters but enter 2 characters
in it, it does not use up 510 bytes (unicode) of disk space to
store those 2 characters. Internally, the database keeps track of
where each field starts in each record. Some fields (memo, OLE,
attachments, MVFs) are not actually stored in-line with the other
data in the record either. So it's not a matter of a single record
being faster to retrieve. There are many other factors at work
here, including whether the database has been compacted recently,
whether you are using page- or record-locking, whether the disk is
badly fragmented, how intelligent are the various layers of
caching (physical on the drive, or the controller, or the
operating system, or in Access), the Rushmore algorithms in JET,
and so on. Then we may have to start another mindset to handle SSD
storage issues as well.


And none of this considers the issue of disk caching, such that
there's not that much difference between data loaded into RAM and
data that is being read from/written to disk, since the latter
usually takes place through the caching mechanism, and is not going
to be limited by the speed of the actual storage medium, but by RAM.
This has been the case in Windows since at least c. 1991-2, when
Windows 3.1 was released with disk caching turned on by default. It
was essential for decent performance in Windows, but it also meant
that your databases were going to be speeded up because of the disk
cache, too (although back then it was largely a read-only
improvement, as lazy writes and such had not been implemented in the
DOS disk cache; any modern version of Windows, though, caches both
reads and writes).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/