Database Performance Optimization through Data Partitioning
Improving database performance is a challenging task that developers need to pay attention to in order to reduce the response time for the users. In this article, I am sharing a technique for Database Performance Optimization using Data Partitioning.
What is Data Partitioning?
Data Partitioning refers to the database process of dividing large tables into smaller parts by Rows and Columns. A major benefit of Data Partitioning is that one only needs to specify the partitioning criteria, and then SQL handles the data by itself.
SQL uses multiple .ndf files (Secondary Database Files), in addition to the .mdf file (Primary Database File) to divide and store data based on the partition criteria. The user needs to specify those .ndf files and provide the partition criteria by using Partition Schemes and Functions. SQL Server then takes care of data maintenance by itself.
Data Partitioning can be divided into two categories:
- Vertical Partitioning: In vertical partitioning, data is divided column-wise. For example, if a database contains a table with 10 columns of which only 5 columns are used frequently, it can be divided into two tables with 5 columns each. This way a table is made more efficient, by dividing it into multiple filegroups based on columns.
- Horizontal Partitioning: In horizontal partitioning, data is divided row-wise. For example, suppose there is a table with 1 lakh of records for the period 2011 – 2019, where each record includes a date. Quite often it is only necessary to access the records for last year, i.e. 2019 or recent years -2017,2018. Through horizontal partitioning, the data can be divided into yearly records in multiple filegroups.
By using these Data Partitioning techniques, we can enhance the performance of our database. This is a highly recommended technique for applications where Reporting is frequently used.
Worked example:
We have a table called tbl Data Partitioning with approximately 2.5 crore records. The data includes records from 2011 to 2014, of which the 2014 records are 45 lakhs. The Table looks like:
To execute a select query to find the 2014 records takes almost 23 seconds.
Using Horizontal Data Partitioning on the table using Created Date, the time can be significantly reduced.
For that, we first need to specify the filegroups:
After creating Partition Functions and Schemes, we can map the partitions to filegroups.
Finally, After implementing Data partitioning, the time taken by the Select query is reduced from 23 seconds to 15 seconds:
Advantages of Data Partitioning:
- Improves performance for larger tables.
- Fast and expensive storage can be used for active data filegroups, while keeping the less active data filegroups on slower/less expensive storage.
- Partial backup and restoration is possible.
- Can be used temporarily as a substitute for data archiving.
Our team has experience of working on PHP MySQL development projects and has handled many assignments from small informational website to robust enterprise applications. For more information https://development.ikf.in/metasys1/php-mysql