Design and Performance of Databases{Comments Off on Design and Performance of Databases}


When it comes to building databases, performance is perhaps the top priority for database engineers. Speed and efficiency is the key when it comes to gathering information using queries for end users. There are various ways to improve the efficiency of databases and that includes the incorporation of vertical and horizontal partitioning. In a peer reviewed article written by Agrawal, Narasayya, and Yang, entitled “Integrating Vertical and Horizontal Partitioning into Automated Physical Database Design,” the authors discuss how vertical and horizontal partitioning can both improve the performance and manageability of databases. Horizontal partitioning takes the rows of tables that have common values and puts them together into one or more tables. Vertical partitioning is the same concept, except using the columns of several tables. Furthermore, there are two main methods of partitioning, as mentioned in the article: hash and range. Hash involves the distribution of rows evenly across different tables with the use of a hash key. Range involves the partition of minimum and maximum values of data in many columns. In order to make the database easier to manage with these methods, it is required to have the indexes and tables aligned. An index is aligned when the index uses the same partitioning technique as the tables. However, achieving such tasks is complex and therefore the physical design is very important. For example, there are many ways to horizontally and vertically partition a database, as well as the alignment and as a result, choosing the correct design is critical. If such designs are not implemented correctly, that database could perform slower or even cause lockups when running queries. Once correctly implemented, the database can receive an improvement of up to 20% in running queries.

This article goes hand in hand with the physical design of a database and it stresses how important it is to choose the best design for the requirements of the business. The performance and efficiency is always the motivating factor and in order to achieve such task, the design is very complex and requires much knowledge from seasoned database engineers.

Even though this article contained many technical terms relating to more advanced database topics, it was an interesting read regarding the many ways of improving the performance of a database. Never did I know that there are plenty of methods to implement in a database that will affect the bottom line. In addition, such tasks require much time and effort to put when designing a database, something I don’t think I will have the patience or even knowledge to do.

Source:

Sanjay Agrawal, Vivek Narasayya, & Beverly Yang. (2004). Integrating vertical and horizontal partitioning into automated physical database design. SIGMOD ’04, 359-370. doi: http://dx.doi.org/10.1145/1007568.1007609