Question
I am aware of the benefits of using partitioning and normalization strategies. Is breaking a database schema into smaller, more query-oriented tables a form of normalization?
I have extensive experience in normalizing tables. Does normalizing tables affect query speed? Additionally, would a subsequent join have an effect on query performance? Furthermore, what can be done to improve query speed in this case? What was the rationale behind the second option being faster? Additionally, under what circumstances would certain columns not be loaded?
Table 1: Employee table with 1 million records, with 25-columns.
query selects
- ID
- Annual salary
- Zip Code
Table 2: Employee table partition with 1 million records, with 3-columns.
query selects all three:
- ID
- Annual salary
- Zip Code
Answer 1
Normalization is the process of cleaning up and removing unnecessary data in order to optimize efficiency. Partitioning involves splitting larger tables into smaller ones to optimize query performance.
Answer 2
Normalization is a process that focuses on the semantic meaning of the data and its relationships (e.g. customer information should be stored in the customer table, customers have orders, and orders have associated line items, etc.). Meanwhile, partitioning involves dividing tables within the database and does not affect the meaning of the data.
Answer 3
Normalization is the process to create an accurate logical data model and is necessary to eliminate redundancy and ensure consistency. Partitioning is a way of physically implementing a table, primarily for the purpose of reducing Input/Output (IO) operations to the disks for certain operations.
Answer 4
Partitioning is the process of splitting data into multiple tables, which enables queries to access data more efficiently by avoiding the need to search a single large table. For example, an organization may partition a database into multiple tables, such as a Salary table, an Employee ID table, etc. This allows the query to pull from multiple tables instead of having to scan a single large table.
Answer 5
Normalization and partitioning both involve reorganizing the columns between tables, yet they serve distinct purposes.
- Normalization is a process that should be performed during the design of a relational database. There are distinct levels of normalization, which are used to minimize repetition and redundancy in tables. This process serves to optimize storage efficiency, particularly when working with relational databases, although it is less essential for NoSQL databases.
- Partitioning is the process of dividing a large table into multiple, smaller individual tables in order to enhance query performance. As fewer data points need to be processed, queries that access only a portion of the data run more efficiently. The aim of this procedure is to optimize query performance. There are two forms of partitioning:
- Horizontal Partitioning (also known as Sharding, which is the term used by SQL Server for partitioned tables) involves separating data into multiple tables of the identical structure. For instance, active data and archive data are typically stored separately. The tables possess the same structure, though they contain distinct data. To differentiate between them, a set of columns may be utilized, such as data older than two years or the first three letters of the last name, guiding the decision of which table to write the data to.
- Vertical Partitioning is when certain columns are relocated from one table to another. This should not be confused with normalization, as the intent and methodology are distinct, even with the transferring of columns between tables. An example of this could be when data is transferred from one table to another for the purpose of enhancing query performance. For example, when a column containing sparsely populated or BLOB data is relocated to a separate table.
Answer 6
Does normalization affect query speed?
Modern multi-core systems can benefit from normalization, as it allows for an additional level of parallelism which can increase the speed of querying.
Does a subsequent join have no impact?
Partitioning can increase the query speed of a base query, however, this does not always hold true. A subsequent JOIN may affect this performance.
What are the advantages of the second option in terms of speed and which columns were not loaded?
This case is distinct depending on the row size. The first table has a larger row size, thus potentially requiring more pages to be read during query execution. However, the number of pages read is largely dependent on the execution plan and the existing indices.
Answer 7
Is there any impact on query speed due to normalization, or does a subsequent join not affect performance?
Yes and no. In general, normalization can improve write performance as it eliminates the need to repeat data multiple times. However, it may lead to slower read performance due to the JOIN operator, which requires the database engine to scan through additional tables in order to retrieve related records. Indexes can help in optimizing the search process for related records across associated tables. Without normalization, JOINs are not necessary but data is repeated multiple times, which is inefficient in terms of storage and can create redundant data.
Due to these factors, many companies have adopted overnight ETL processes that populated a denormalized database specifically created for reporting and data analytics. However, JOINs in this context can cause analysis to be sluggish. As a result, the OLTP and OLAP paradigm has developed. In many of today’s big data systems, storage efficiency has become less of a priority due to decreases in storage costs. To support this, databases such as NoSQL have been developed, in which join operations are not favored, and denormalization is the standard.