Rick Debay wrote:

> I don't know how the database caches table data, if a whole row goes in
> the cache or only the columns referenced, so I want to make sure this
> will be a useful optimization.

The cache holds database pages. Those that are data pages contain
compressed record data. If you have a small number of columns that are
changed frequently, it might make sense to put them in one table and
leave the larger number of more stable data in the other table. That
way, you get more volatile data on each read. I would count frequent
read data as well as frequently updated.

While records are being processed, they are kept in buffers which are
large enough to hold the largest possible record for that table -
specifically, varchars are laid out at full size. All fields are
present. Relatively few records are kept at any one time, so this is
not a very important consideration.

Sort is handled differently, because sorting often requires having lots
of records in memory at once. When records are being sorted, only the
required fields are included.



Regards,


Ann