One sure way to improve database performance
This module talks about physical design methods you can use to improve database performance for some operations,
but you will see that every gain from rearranging data on a disk results in a loss somewhere else.
There is only one sure-fire way to improve database performance, and that is by upgrading the hardware.
An RDBMS accomplishes a multitude of tasks: it finds values in tables, compares values in fields, joins table rows, and writes those rows
to RAM. A faster processor (CPU) lets the computer find and compare values more quickly –
lots of RAM (in the gigabytes for large databases) means the computer can manipulate table contents without writing anything to disk,
which offers the biggest savings of all.
If you are thinking about upgrading your computer, try adding all the RAM possible, then moving to a faster processor.
To determine whether adding indexes will improve the performance of the system.
One approach to selecting an appropriate file organization for a relation is to keep the tuples unordered and create as many secondary indexes as necessary. Another approach is to order the tuples in the relation by specifying a primary or clustering index. In this case, choose the attribute for ordering or clustering the tuples as:
- the attribute that is used most often for join operations, as this makes the join operation more efficient, or
- the attribute that is used most often to access the tuples in a relation in order of that attribute.
If the ordering attribute chosen is a key of the relation, the index will be a primary index; if the ordering attribute is not a key, the index will be a clustering index. Remember that each relation can only have either a primary index or a clustering index.