Databases can store tremendous amounts of information, petabytes of information. Specific bits of data are accessed by queries written in a particular interface language, such as SQL. Database optimization involves maximizing the speed and efficiency with which data is retrieved. Database designers, administrators and analysts work together to optimize system performance through diverse methods. Thoughtful design that carefully addresses functional needs is the foundation of performance enhancement.

The construction of queries can yield faster results, as well. Administrators and analysts seek to improve their servers’ data access methods and retrieval times through design techniques, statistical analysis and monitoring of system traffic. In this role, you endeavour to make the most of your server’s potential by possessing a strong knowledge of the structure of the data, the applications installed on the server and the impact varied tasks have on the database’s overall performance.

The goal of database performance tuning is to minimize the response time of your queries by making the best use of your system resources. The best use of these resources involves minimizing network traffic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of your data, the applications used on your system, and how the conflicting uses of your database might affect performance.

There are various ways by which SQL databases can be optimized.

♦   Indexing method : Index is basically a data structure that helps speed up the data retrieval process overall. Unique index is a kind of indexing that creates separate data columns without overlapping each other. Proper indexing ensures quicker access to the database. Excessive indexing or no indexing at all are both wrong. Without any indexing at all, the processing will be very slow, whereas indexing everything will render the insert and update triggers ineffective.

♦   Retrieve the relevant data only : Specifying the data one requires enables precision in retrieval. Using the commands * and LIMIT, instead of SELECT * as and when required is a great way of tuning the database, while avoiding retrieving the whole set of data when the user wants only a certain part of it. Of course, it will not be necessary when the amount of data overall is less. But when accessing data from a large source, specifying the portions required would save a lot of essential time. The * command is for use in specifying data from columns, and the LIMIT command is when the user requires data from a certain number of rows from among the lot. Selecting sparingly is not exactly a necessary rule. However, it does help in avoiding system errors in the future. Also, limiting and specifying the data reduces the further necessity of optimizing of the database to a great deal.

♦   Getting rid of correlated sub-queries : A correlated sub-query basically depends on the parent or outer query. This kind of search is done row by row. That means it decreases the overall speed of the process. This problem usually lies in the command of WHERE from the outer query, applying which, the subquery runs for each row, returned by the parent query, consequently slowing the whole process and reducing the efficiency of the database. So, a better way of tuning the database, in this case, is to the INNER JOIN command, instead of the correlated sub-query. But in certain cases, using correlated sub-query is essential.

♦  Using or avoiding temporary tables according to requirement : If any code can be well written in a simple way, there is absolutely no need to make it complex with temporary tables. Of course, if a data has a specific procedure to be set up which requires multiple queries, the use of temporary tables in such cases are, in fact, recommended. Temporary tables are often alternated by sub-queries, but one has to keep in mind the specific efficiency that each of these would provide in separate cases.

♦  Avoid coding loops : Avoiding coding loops is very much needed in order to avoid slowing down of the whole sequence. This can be achieved by using the unique UPDATE or INSERT commands with individual rows, and by ensuring that the command WHERE does not update the stored data in case it finds a matching pre-existing data.

♦  Execution plans : The execution plan tool created by the optimizer play major role in tuning SQL databases. They help in creating proper indexes too. Although, its main function is to display graphically the various methods to retrieve data. This, in turn, helps in creating the needed indexes and doing the other required steps to optimize the database.