Database Reference
In-Depth Information
The DBA makes only
approved modiications
to the production
programs
Test System
Production System
Move/copy
utility
program
Test
programs
Production
programs
270
DBMS
DBMS
Programmer
User
e
s
u
c
DBA
b
a
b
The DBA makes only
approved modiications
to the production
database
FIGURE 8-9
DBA controls the interaction between the test and production systems
A production system with a DBMS is a complex system. Having a separate test system reduces the com-
plexity of the production system and provides an extra measure of control.
Performance Tuning
Database performance deals with the ability of the production system to serve users in a timely and respon-
sive manner. Because funding is usually a constraint, the DBA
'
s challenge is to get the best possible perfor-
mance from the available funds.
Faster computers with faster disks, faster network connections, faster software, and other production
system expenditures help improve performance. What can the DBA do if the organization has no additional
money for its production system but needs further performance improvements? The DBA can change the
database design to improve performance; this process is called tuning the design. Some of the performance-
tuning changes the DBA can make to a database design include creating and deleting indexes, splitting tables,
and changing the table design.
By default, Access and some other DBMSs automatically create indexes for primary key and foreign key
fields. These indexes make accessing the fields faster than accessing would be without the indexes. Further,
indexing common fields improves the speed of joining related tables. If a DBMS doesn
t automatically index
primary key and foreign key fields, the DBA should create indexes for them. In addition, queries that search
indexed fields run faster than comparable queries without indexes for those fields. For example, if users fre-
quently query the Part table to find records based on values for the Class or Warehouse fields, the DBA can
improve performance by adding indexes on those fields. On the other hand, a table with many indexes takes
longer to update. If users experience delays when they update a table, the DBA can delete some of the table
'
'
s
indexes to improve updating performance.
If users access only certain fields in a table, you can improve performance by splitting the table into two
or more tables that each have the same primary key as the original and that collectively contain all the fields
from the original table. Each resulting table is smaller than the original; the smaller amount of data moves
faster between disk and memory. For example, suppose dozens of users at Premiere Products access the
Customer table shown in Figure 8-10.
