HomeSoftwareDatabase Optimisation, a Continuous Process not an Event

Database Optimisation, a Continuous Process not an Event

Best Freelancers to help you Grow your Business

Databases are a critical component of applications that power small and large businesses. The information stored in the databases gets frequently retrieved or accessed by applications to meet the demands of the users, who will rely on the output as input into other processes. It is vital to ensure fast access to retrieve records stored in the database within a reasonable threshold.

To ensure that information is readily available when needed, some tasks can be carried out once in the lifetime of an application, and there will be no issues, whereas others must be done regularly for the life span of the application.

One critical activity that falls under a continuous task is database optimisation, and attempting to wrongfully categorise this will lead to disaster in the performance of the application or the database itself.

In this article, I will focus on what optimisation is, optimisation approaches, benefits, and challenges of optimisation. The techniques and examples I will share are all based on some interventions in resolving database performance degradations in my database administration journey.

What is Database Optimisation?

Database optimisation is analysing various database components, including SQL queries and tuning them to improve database performance. It is an iterative process to maximise the speed and efficiency of data retrieval.

It requires collaboration among all the stakeholders (application developers, database engineers, network engineers, and system engineers) to implement the required adjustments that will result in the improved performance of the database.

Most of the database and application setups work efficiently at the initial stage, but as the data grows, the performance may become degraded. The degradation may be in the form of queries running slower, CPU usage spikes, or slow I/O responses. These bottlenecks are signs that regular review and analysis of the database are essential to identify and fix deviations.

- Advertisement -Protect and Monitor your Kids

Database Optimisation Approaches

Several techniques can be adopted in optimising databases for better performance; some of these are as follows:

1. Make use of indexes

Indexes help to improve the performance of queries running on the database by reducing the number of scan, sort, or join operations. It is essential to identify columns used to limit record retrieval in a query and create indexes on such columns to speed up the query execution.

For instance, whenever queries run slowly, you can check the columns specified in the WHERE clause, confirm if they are indexed and if not, create appropriate indexes on them.

It is crucial to note that while indexes will make read operations faster, they can slow down write operations because indexes will be updated whenever changes are made to the tables.

- Advertisement -Best WordPress Hosting 2024

2. Data retrieval

In most cases, the scripts that result in database performance degradation were found to be retrieving more records than needed. For example, selecting all the columns in a table when you only need a few columns to meet the requirements of the application module. Reducing the amount of record retrieval per time will reduce the pressure on the CPU and other hardware resources.

3. Batch DML (Data Manipulation) operations

Data manipulation languages include insert, delete, and update; these languages make changes to the records stored in the database, and they must be carefully used to improve database performance.

Making changes, such as deleting or updating a large chunk of data at a time, can take longer to complete, leading to waits by other processes due to the lock mechanism placed on the table to ensure data consistency.

The waits for other processes while making changes to records in a table can be reduced by batching the DML operations instead of carrying them out as a single transaction. Batching long-running activities will save time during rollbacks in the event of disruptions when the query is executing.

4. Database Statistics

The optimiser makes use of the statistics on the table to determine the choice of execution plan to use for queries. If the statistics on the tables are stale, the optimiser may not choose the best

execution plan that will return faster. Not using the best execution plan will not only affect the speed of query execution but will also place much pressure on the hardware resources (CPU, memory, and I/O) of the system. It is a good practice to periodically update statistics on the database objects to ensure the statistics are not at variance with the actual records in the database.

5. Index Fragmentation

Changes to the database, such as deletion, update, and insertion of records, lead to fragmentation of the indexes, which can lead to poor database performance. The best practice is to periodically check the index fragmentation percentage in the database and implement maintenance processes such as index rebuild or reorganisation to remove the fragmentation.

6. Query Optimisation

It involves reviewing and analysing queries running on the database for possible improvements.

EXPLAIN PLAN can be used to understand the query execution plan and where most of the cost lies. Once the highest cost is identified, some adjustments can be implemented, such as re-arrangements of the columns used in the where clause and a review of the use of functions to eliminate bottlenecks.

7. Proper Sizing of Hardware Resources

As the data stored in the database grows, there may be a need to increase the resource allocation to meet the increasing demands. However, increasing the resource allocation every time may not be the solution; rather, it will be necessary to review other parameters to make the right decisions.

In some cases, if the statistics on the tables are not correct, the optimiser can choose an execution plan that seems appropriate based on the available statistics, which may end up causing a CPU spike. It is pertinent always to check that the queries running on the databases are reviewed and tuned to ensure that hardware resources are only increased or decreased based on proven facts.

8. Connection Pooling

It reduces the cost of opening and closing connections by maintaining a pool of open connections that can be passed from one database operation to another. It helps manage and reuse database connections efficiently. The impact of connection pooling may be less evident in small applications than in large ones, and implementing this should be carefully considered.

9. Partitioning

It involves dividing large tables into smaller and more manageable pieces. Query performance can be enhanced by allowing the database engine to work on a smaller subset of data, resulting in faster query execution. If partitioning is properly implemented, it eliminates the need to scan the entire dataset but only the partition of the dataset specified in the query.

10. Data Archiving

This is the process of detecting obsolete data that is no longer required in real time for the program to function properly. Such records can be archived or deleted to reduce the number of records accessed, improving query performance and lowering storage requirements. For example, tables with huge historical datasets or logs should be archived.

Benefits of Database Optimisation

Database optimisation offers several benefits to organisations as follows:

  • Cost saving: Optimised databases will require fewer resources, reducing the cost of hardware resources.
  • Improved user experience: Faster data access/retrieval enhances user satisfaction.
  • Increased query efficiency: Complex queries can execute faster without experiencing delays.
  • Scalability: Optimised databases can handle more concurrent requests and large volumes of data.

Challenges of Database Optimisation

  • It is time-consuming: optimising databases requires much time and expertise to identify bottlenecks and proffer solutions.
  • Complexity: Making multiple adjustments to configurations can create complications in the maintenance of the database system.

Conclusion

Database optimisation is not a one-time event but a continuous activity that must be carried out throughout the lifespan of the database. It involves many considerations to decide which part of the database to tweak, leading to performance improvement or degradation. Setting and having a performance baseline helps to spot any deviation in the performance of the database quickly.

Related Topics

Find the Best Freelancers
NaijaTechGuide may receive financial compensation for products/services purchased through affiliate links on this site. See full Affiliate Disclosure Here
Olutayo Awodele
Olutayo Awodele
Senior Database Consultant, Polaris Bank Limited

Recommended Read on NaijaTechGuide

Best Marketing Automation Software 2024

You need the best marketing automation software to scale your business fast and convert...

Best Content Marketing Tools for 2024

As a content marketer, you have a lot of tasks to accomplish. You need...

Best VPN Services Providers for 2024

The internet is one of the greatest achievements of humanity, but it is also...

Best Laptops for Students 2024: 11 Top Picks to Choose From

So, you are a student who is looking to get the best laptop they...

Samsung Phones and Prices in Nigeria 2024

Samsung Galaxy Phones are the secret of Samsung's dominance in the smartphone market. Samsung...

Cheap Android Phones 2024 – Price, Specs, and Best Deals

Are you looking for the best cheap android phones in the market right now?...

Best Android Phones 2024 – Price, Specs, and Best Deals

New Android Phones are launched into the market every week. This means that if...

Best Web Hosting Services for Building Websites in 2024

A good and reliable web hosting service will make a big difference for your...
Find the Best Freelancers

More like this

6 Tips for Successful Implementation of Venture Capital Software

The global venture capital market is increasing. In 2024, it’s expected to reach a...

10 Benefits of Call Tracking to your Business

Before we look at the major and important benefits of Call Tracking first let...

GetGlobal By TeleSoftas Partners with CGI School to Train and Match Nigeria Talents in Architectural and Product 3D Modeling

We are thrilled to announce an exciting partnership between GetGlobal By TeleSoftas and CGI...