Sams Teach Yourself SQL in 24 Hours, Third Edition By Ronald R. Plew, Ryan K. Stephens
Table of Contents
Hour 17. Improving Database Performance
Before continuing with your SQL statement tuning lesson, it is important to understand the difference between tuning a database and tuning the SQL statements that access the database.
Database tuning is the process of tuning the actual database, which encompasses the allocated memory, disk usage, CPU, I/O, and underlying database processes. Tuning a database also involves the management and manipulation of the database structure itself, such as the design and layout of tables and indexes. Additionally, database tuning often involves the modification of the database architecture in order to optimize the use of the hardware resources available. There are many other considerations when tuning a database, but these tasks are normally accomplished by the database administrator. The objective of database tuning is to ensure that the database has been designed in a way that best accommodates expected activity within the database.
SQL tuning is the process of tuning the SQL statements that access the database. These SQL statements include database queries and transactional operations such as inserts , updates, and deletes. The objective of SQL statement tuning is to formulate statements that most effectively access the database in its current state, taking advantage of database and system resources and indexes.
Both database tuning and SQL statement tuning must be performed to achieve optimal results when accessing the database. A poorly tuned database may very well render wasted effort in SQL tuning, and vice versa. Ideally, it is best to first tune the database, and then ensure that indexes exist where needed, and then tune the SQL code.
