Top Tips For Resolving Poor Performance in Database Design
[ Home Page - Surveys - Documents - Presentations - Search ]
This document provides top tips which can help to ensure that databases are created that can be easily exported and manipulated with the minimum of difficulties.
The majority of database performance issues are caused by un-normalised or partially normalised data. Normalisation is the technique used to simplify the design of a database in a way that removes redundant data and improves the efficiency of the database design. It consists of three levels (1st, 2nd and 3rd) normal forms that require the removal of duplicate information, removal of partial (when the value in a field is dependent on part of the primary key) and transitive (when the value in one non-key field is dependent on the value in another non-key field) dependencies.
About 70% of good SQL performance can be attributed to proper and efficient indexes. Indexes are used to provide fast and efficient access paths to data, to enforce uniqueness on column values, to contain primary key values, to cluster data, and to partition tables.
Indexes have many benefits, but they have disadvantages. Each index requires storage space and must be modified each time a new row is inserted or deleted, as well as each time a column value in the key is updated. You should ensure that indexes are only used when necessary. In many circumstances it may be more appropriate to modify the structure of an existing one? Use the EXPLAIN statement to find the access path chosen by the optimiser.
Ensure the query is structured correctly by rechecking the WHERE clause. Are the host variables defined correctly and are the predicates designed to use existing indexes?
Unnecessary data sorting can also have a detrimental impact upon processing speed. You should ensure that all sorts (ORDER BY, GROUP BY, UNION, UNION ALL, joins, DISTINCT) only refer to the necessary data.
When developing stored procedures (a series of SQL commands), use the SET NOCOUNT ON option at the start of your procedure. This will prevent the superfluous "row count" messages from being generated, and will improve performance by eliminating wasted network traffic.
Remove unnecessary JOINS and sub queries - Would the application be more efficient without the join or sub-query? Are simple or multiple queries more efficient?
Many problems can be encountered when connecting to an organisational database from home or anywhere outside the faculty. Many delays are caused by DNS lookup timeout. Check that the database server can resolve the IP address. If the intervening firewall uses NAT, then the IP address will match the firewall's interface closest to the database server. If you are troubleshooting the connection, gather more information using 'tcpdump' and examine the packet timings to determine where the delay is occurring.
Many performance issues are caused by the host application rather than the database itself. When identifying performance issues it is useful to perform an Internet search using application keywords to identify problematic combinations. For example, tests have found that the use of a MS Access database run from a NetWare server can dramatically increase the query time if the database is not stored in the drive root.
Though it is theoretically possible to use SQL (Structured Query Language) script files between databases, the range of implementations in desktop databases differ. This may cause significant delays. In practice code needs to be recreated altered to account for implementation differences.
[ Home Page - Surveys - Documents - Presentations - Search ]
Web page by Brian Kelly of UKOLN.
Last Modified 11-July-2004
Email comments to webmaster@ukoln.ac.uk