Database Developer and Administrator Essentials - Performance Tuning Tips For SQL Database

• By Walter Moore
• 14-06-2021
• Technology

For the newbies, performance tuning of SQL DB may be a fairly difficult task to accomplish. This is more so when you happen to work with huge datasets in which even the minor changes, too, have a highly dramatic impact on the overall performance of the DB (both positive and negative impact). In any small, mid, or big-sized companies, most of the part of SQL performance tuning may be handled by an expert DBA (database administrator). However, many developers are bound to perform such tasks as performance tuning. In many cases, DBAs themselves struggle while working with developers for database tuning. It requires various problem-solving techniques, which may sometimes even lead to disagreements between the admins and development teams.

On top of the above, the enterprise set up may also play a crucial role. For example, the DBA team may be functioning on a different floor with all the company data servers placed at their end, whereas the developers may be on another floor. Sometimes, these teams may be functioning in different buildings, making it hard for them to work together and smoothly undertake the performance tuning needs. Considering all these factors and with the inevitable need to fine-tune the database, here in this article, we will discuss a few essentials like how the developers can perform SQL performance tuning and how they can work together with DBAs effectively.

SQL DB performance tuning

Performance tuning in the codebase

If you are a newcomer to the database management platform and confused about performance tuning? You are not alone. At the first point, you should know that indexing is the most effective mode of tuning the SQL database, which is frequently neglected by the developers during the time of development. At the baseline, indexing is putting a proper data structure, which will help enable data storage and data retrieval speed. With proper indexing, you can easily sort your rows and data tables faster.

Indexes are also used to define the primary key or a unique index that will guarantee that no other columns in the database hold the same values. Database indexing is a vast topic for sure, but it is highly interesting too. If you are new to indexing, you may take some expert consulting assistance during the phase of development.

The basic goal of indexing is to facilitate major searching on the database and ordering the columns. If your tables are hammered constantly by the commands of INSERT, DELETE, and UPDATE, you have to very careful about the indexing process. This is because all the indexes need to be modified after these, which may decrease overall performance if not done carefully.

It is also noted that DBAs often drop SQL indexes before performing the batch inserts of a million or more rows to speed up insertion. After a batch gets inserted, they tend to create indexes. However, while doing this, one should also remember that dropping the indexes will affect every query running on the table, so this approach is recommended only when working on a single large insertion. For those who are new to it or need to get expert assistance in database administration, RemoteDBA.com can offer reliable services in remote DB management.

SQL Server Execution Plan

There is an execution plan tool in SQL Server, which can be effectively used to create indexes. This tool's major function is to display the data retrieval methods graphically as chosen by the query optimizer. To retrieve the execution plan by accessing the SQL Server Management Studio, you have to click on the "Include Actual Execution Plan" or use the shortcut key of 'CTRL + M' before running the query. Once you do this, you will get another tab as "Execution Plan." You may see a missing index detected at this point. To create this, you click on the execution plan and choose the "Missing Index Details" to get it done. While doing this, ensure that the 'WHERE' clause you put avoids updating any stored value by getting it matched to any existing values.

Avoiding correlated SQL subqueries

SQL correlated subquery is something that uses values from parent query, and this runs, once for each of the rows as returned by query. It will thus decrease the SQL query performance, and it seems that new SQL developers are often caught by structuring their queries this way as it is the easiest mode of doing it. The problem here is that the inner query may be run for each row, returned by the outer query. However, it is not advisable to go over the inner query repeatedly for each row processed by an outer query. A more competent performance tuning technique for the SQL database is to refactor correlated subqueries as a join. With this, we may go over the inner queries only once at the beginning and then join it with the outer queries so that the values are selected more efficiently.

Using temporary tables

The usage of temporary tables tends to increase the complexity of a query. If you can write the code more simply and straightforwardly, temporary table usage can be avoided. However, if there is a stored procedure for data manipulation that cannot be handled using a single query, you have used the temp tables to generate the final result.

When there is a need to join a larger table and set conditions on the said table, you may increase the database performance by transferring the data in the temporary table and joining for the same. Here, your temporary table may have only fewer rows than the original table, so the join will quickly finish. However, the decision to create temporary tables or not is always easy and straight hood. It would help if you gave careful consideration based on your need at hand.

So, the SQL database's performance tuning includes SQL client code optimization, query optimization, and index management, which all points to the need for better coordination between the DBAs and the developers involved in enterprise database management.