In this article I will summarize some vital considerations that can help DBAs, Database Architects as well as Application Developers designing and tuning a database system so that the application and the database performance is as at its best.
When designing/tuning applications and their relational database environment there are several key issues besides the actual tables that the application development team must have in mind.
A good knowledge on the SQL query language for the targeted database to be used. However, as the SQL query language is in general the same and differs slightly in some areas between RDBMS then the designer can easily transfer his/hers knowledge to different RDBMS.
Use, where possible, all vendor recommended query tips and tricks that lead to better performance as described in the database SQL manuals.
Use, where possible, the same columns in a where clause in more than one query to minimize the amount of indexes needed.
Having the tables well normalized will certainly give you performance benefits in data maintenance especially on updating the data. For example, updating data in one single row is faster and more efficient than updating same data in multiple rows.
Use the correct datatypes to minimize expensive datatype casting in queries. This is true in particular with columns appearing in where clauses.
Applying indexes to the tables that match the columns of the where clauses and/or group clauses of all SQL queries that the application are using (or will use) are vital to the application and database performance, as index scan is far more cheaper than a full table scan in respect of CPU and time. Remember: Favor index scan over table scan.
Look at the column statistics of a table when you decide for the order of the columns in an index.
Make sure that all matching columns (predicates) are preceding range columns (predicates) in the index. As indexes are made of a tree structure, another way to look at the columns and how to organize them in an index is to draw a tree or a pie in your mind (or on a piece of paper) and make sure that each slice is as small as possible and the search on each levels of a tree is as short as possible. That is, the traversal on the tree from top to bottom in the search for the right row is as short as possible.
In many database environments you have the ability to assign a container (tablespace, indexspace, filegroup) to either table or index. Splitting table data and indexes to separate containers, and if possible putting them on separate hard drives, will definitely give you some performance boost which you will mostly see in the form of reduced I/O on the disk.
Separate large tables into single tablespace from smaller tables avoiding bufferpool and i/o queueing of data. Also separate frequently accessed tables/indexes into separate tablspace/indexspace to avoid queueing of data.
Having several bufferpools in your database where each bufferpool is dedicated to a tablespace or indexspace, and where you have the bufferpool size at least 1.5 times the size of the largest table (in data pages) in the tablespace, you will see some performance benefits. Some RBDMS have the ability, if configured, to automatically tune the bufferpools based on usage statistics. This can be very useful for beginners to start tuning the database, however I can't see yet the benefit using this configuration on a busy server that has both web server and a database server. There the underlying operating system will most likely go into the situation where the web server and the database server are fighting over available memory, resulting in performance degradation. If your servers have excess memory then I strongly suggest to allocate enough memory permanently to the bufferpools the size I mentioned earlier.
Tablespaces as well as indexspaces that are dedicated to the database catalog tables and indexes should have dedicated bufferpool(s) and no other tablespaces or indexspaces should be assigned to it. This is to ensure that the core of the database will run as smooth as possible.
Database I/O considerations:
Frequently accessed tables and indexes are placed on separate disks to balance I/O and prevent read queuing. Also frequently joined tables and indexes.
Use all vendor recommended parameter settings for underlying disk systems and tune according to vendor best practices.
Here I have talked briefly about the key issues to make your application and database system perform at its best.
When designing and tuning application and database systems you should always consider the vendors best practices as well as these tips I have provided and also look for and measure other tips specific for your RDBMS system found on the Web or books.