Database Optimization
Database efficiency is often overlooked by programmers as well as web designers. It is quite common not to optimize the database. Improving the efficiency of the database can have a big impact on its long-term stability. Having the right structure and data type is the building block for an efficient database and is mandatory for any professional web application.
Database management systems are pervasive in the modern world. The notion of a persistent, redundant, and highly distributable library of information has become the single most important concept in our information technology repertoire. In fact, virtually every human being in the Western world interacts with a database management system of some kind on a daily basis—often without using a personal computer at any time throughout the day.
The purpose of this paper is to discuss basic database optimization using mathematical cost estimation for different types of queries, a review of join performance, and the effects of various physical access structures on specific query examples.
The intended audience should be familiar with SQL and basic relational database concepts – typically an experienced database developer. Specific examples will be given in the context of MS SQL Server 2005, but the concepts they illustrate will be general enough to apply to any SQL-supporting relational DBMS (Database Management System).