Three Simple Techniques to Get Started With Database Optimization

1. 3 Simple Techniques to Get Started With Database Optimization

2. Getting into database optimization as a beginner can be overwhelming. We understand that. There are so many aspects to the optimization processes that database administrators carry out like: explore, design, implement and validate scenarios. It’s normal to feel intimidated by it. We have almost two decades of experience working with databases and tuning them and we have learned a lot along the way.

3. What is Database Optimization? Database optimization is the process of analyzing various components of the database including SQL queries and tuning them to improve database performance. The initial database design, or the DB server hardware, or even the initial queries might work efficiently at the beginning.Bottlenecks might appear, queries might run slowly, or you might run out of storage.

4. Optimization Techniques

5. 1. Changing The Query Structure - There are times when how the query is written leads to long execution times. The only way to speed it up would be to rewrite the query or make changes to it. One of the most often ignored rules that require changes is queries that use “*” to select all fields. It’s always faster to list the columns that need to be extracted instead of using “*” which fetches all the columns increasing the execution time.

6. 2. Changing the DB structure At times, we have no option but to change the structure of the DB or the DB design to ensure the database performance expectations are met. Changes can be minor or require the entire table to be redesigned. Here, let’s look at some simple changes that can be made to the DB structure.

7. Pre-calculating fields -If there are operations where you need to constantly compute constant values, then it’s better to do compute them while writing it into the DB and add that value to a new column in the table. • Denormalize the database -One of the tasks under database optimization that we carry out is the normalization of the DB schema. This ensures that the data redundancy is reduced and the integrity of the data increases • Split tables to handle large data - Certain types of data are inherently large like images or videos or sizable textual data. Read time for a query depends on the data size so, if the data size is very huge, then it becomes very difficult for the query to run efficiently.

8. 3. Execution Plan and Caching SQL query execution plan is the set of steps that the DB optimizer has derived that tells how the query needs to be executed. Computation of this plan is a time taking process as the database needs to evaluate many possible options and find the best one. This is where caching can help. Database caches cannot only store the query responses but also the query plans.

9. Conclusion Database optimization is surely a vast topic with a lot of aspects to consider and a lot of parameters to tweak and it can get intimidating when starting with it. However, taking things slowly one at a time will help to get a grasp on them. Here, we have seen very simple checks and measures to optimize various parts of the database and a SQL query. As I’ve mentioned earlier, just because they are simple doesn’t mean that they aren’t used. In fact, they are very powerful and we DBAs use them often in our processes.