Mathematics of Joins, Denormalization, Rows Per Block and IO

i’ve been asked over and over again about the joins, and the mathematics behind the data vault.  this is a brief look in to this subject.  there are a number of arguments out there, and hundreds of articles that discuss the principles of normalization vs denormalization.  over the next few entries i will bring some of these arguments to bear on the data vault model architecture.  however, there is a common belief that denormalization (or removal of joins) will gain you performance.  this is true, but only up to a certain point.  the purpose of this entry is to explain (with finality) the nature of denormalization and it’s relationship to the number of i/o’s that are executed to insert and select data in a common relational database system.

first the assumptions

i hereby assume the following to hold true for everything stated in this entry:

  1. the data is raw data
  2. the columns are not compressed via any mechanism
  3. the blocks and rows are not compressed via any mechanism
  4. i am not discussing appliances, nor columnar database systems, nor nosql alternatives, i am referring to traditional rdbms systems with standard block sizing and i/o calls, because this appears to be (today) over 85% of the market share for the enterprise data warehousing world.

defining denormalization

denormalization is the process by which columns are “moved” from 2 or more tables, and combined in to a single table.  the idea is to remove the joins between the two tables, and increase performance.  denormalization from a data modeling perspective is the act of going “up” a level (ie: from 3rd normal form down to 2nd normal form, then down to 1st normal form).  it causes repetition (copies of data) in the columns.  in other words, if you start with two table structures (ie: name and address), you would combine them to form a single table with both name and address contained – that would be an example of denormalization.

that said, what happens when you denormalize data?

  1. the row width of the denormalized table becomes larger.  in other words, it’s the sum of the row-width of the two or more normalized tables that have been combined.
  2. table scans become more frequent
  3. rdbms engine optimizers rely more heavily on read-ahead buffering, and potentially horizontal (range) based partitioning for partition elimination techniques.
  4. the entire row of data must be fetched in to ram regardless of how many columns you want or need.  examples can come later, in other words: all columns of all rows are necessary to be “read in” to meet the demands of every query accessing any data in this table.
  5. over-indexing becomes more important – in order to find the right row faster, and eliminate any possible block scans that may happen, the wider the row gets, the more indexes are needed to avoid full table scan operations.
  6. indexes on extremely wide tables can take as much as 3x the amount of space (to keep performance acceptable) when compared to the raw data size of the table.
  7. data lay out across mpp nodes is consistently “kept together”, assuming that your queries will be accessing the data all together on a consistent basis.

defining normalization

normalization is the process by which columns are “separated” from a single table in to smaller multiple tables.  another name for this activity is vertical partitioning (splitting data by column).  normalization from a data modeling perspective is the act of going “down” a level (ie: from 3rd normal form to 4th, then 5th, then 6th).  an example of 6th normal form would be a column based appliance (which is outside the scope of this discussion, so no further references will be made).  if you start with a single table (customer details), then split it in to name, address, and personal information – you would be effectively normalizing the data set.

that said, what happens when you normalize the data?

  1. the row width of each table is respectively smaller (on a percentage scale).  the actual width depends on the header pointers + the size of the columns in the row.
  2. table scans are less frequent, and are replaced by joins.
  3. single indexes become more important – especially the indexes used for joining.
  4. clustering data by the primary key (for join purposes) can really help speed performance of the joins
  5. data layout (on mpp) forces each of the tables to be spread evenly across the nodes
  6. rdbms engine optimizers rely heavily on cost optimization, table and column elimination techniques, and index join algorithms
  7. rdbms optimizers begin to rely heavily on parallelism, proper data distribution, and partitioning of the data sets.
  8. queries determine which tables and what data is needed to answer the question, not all rows of all blocks of all tables are necessary to meet the demands of every query.

what about the mathematics?

you have been asking, so i will do my best to answer.  if someone with a phd or masters in physics or math would care to help me out, that would be most welcome.  i have neither of these.  let’s take a look at “simple mans math” – because i am a simple man.

assumption: 64kb data block sizes (not talking file allocation block size here, i’m referring to database block size allocation).  let’s also assume 100% full (0% free, 100% fillfactor), 0% overhead, zero updates, 100% inserts – our tests will be all about the performance of sql select statements – select * from table is the query, just to be fair.  assumption: zero fragmentation in the database (as we all know, this affects performance adversely regardless of data modeling technique)

row sizing:

  • 1k row size = 64 rows per block
  • 2k row size = 32 rows per block
  • 4k row size = 16 rows per block
  • 8k row size = 8 rows per block

now, for a single table, executing the query above, 100m rows to select:   let’s assume one more thing: each i/o (block read) takes 1 milliseconds (0.001 seconds)

  1. 1k / 1: 100m / 64 rows per block = 1,562,500 i/o’s to read all the data off disk * 0.001 seconds = 26.041 minutes to read all rows
  2. 2k / 1: 100m / 32 rows per block = 3,125,000 i/o’s to read all the data off disk * 0.001 seconds = 52.083 minutes to read all rows
  3. 4k / 1: 100m / 16 rows per block = 6,250,000 i/o’s to read all the data off disk * 0.001 seconds = 104.16 minutes to read all rows
  4. 8k / 1: 100m / 8 rows per block = 12,500,000 i/o’s to read all the data off disk * 0.001 seconds = 208.33 minutes to read all rows

now, remember this: the time is sequential time.  why?  because at this point i’m assuming zero parallelism, and 100% table scan to fetch all data for all rows off disk, i’m also assuming a clean ram buffer (no buffering on the disk device, and no buffering in he database ram area).  this is strictly disk performance.

let’s take a look at the table normalized: (we will assume there will be a parallel factor of 4 – ie: split the tables up in to 4 equal parts, so as to give each sized row an equal parallelism count)

  • 1k table : normalized to 4 * 256 byte tables  by 64kb blocks = 256 rows per block
  • 2k table : normalized to 4 * 512 byte tables by 64kb blocks  = 128 rows per block
  • 4k table : normalized to 4 * 1k tables by 64kb blocks = 64 rows per block
  • 8k table: normalized to 4 * 2k tables by 64kb blocks = 32 rows per block

now, let’s check back in with the math for each single table (for which we currently have no calculations). remember the block size is fixed to 64kb for this example.

  • 1k / 4: 100m rows / 256 rows per block = 390,625 i/o’s to read all the data off disk * 0.001 seconds = 6 minutes to read all the rows
  • 2k / 4: 100m rows / 128 rows per block = 781,250 i/o’s to read all the data off disk * 0.001 seconds = 13.020 minutes to read all the rows
  • 4k / 4: 100m rows / 64 rows per block = 1,5262,500 i/o’s to read all the data off disk * 0.001 seconds = 26.041 minutes to read all the rows
  • 8k / 4: 100m rows / 32 rows per block = 3,125,000 i/o’s to read all the data off disk * 0.001 seconds = 52.083 minutes to read all rows

the rest off the calculations for 1k and 2k tables are above, and already listed.

now: the parallelism part of the test.  assumptions first:

  1. i assume that the rdbms engine is capable of running parallel query
  2. i assume that the rdbms engine has been properly tuned to run parallel query
  3. i assume that there are no i/o or other bottlenecks that stand in the way of the optimizer choosing parallel query (in other words, i assume that the optimizer will always run parallel query)
  4. i assume that there are 4 parallel query threads possible, and that all 4 can run concurrently without “blocking” – in other words, the rdbms has been properly configured, and in fact now chooses to run 4 parallel query threads for this test.
  5. i assume there is some added overhead for merging results together (to be assigned based on case)

what about the performance?

ok, so here we are.  my claims are that normalization (within reason), of a single table can actually a) scale better, b) perform better with joins, when compared to a single table scan of a wide table structure.  my answers are based in run-times, the mathematics of parallelism & partitioning (the division of both data and processes).  it comes down to this: you can test the pieces above in your own rdbms system, i’d be curious to know what you find for out-comes.  perhaps the rdbms vendors are “missing something” in the parallel computing field that is holding back the performance of their joins…  what ever the case is, please comment on this post, add your own performance test cases so we can all see what different vendors actually do (speed wise).

based on the case above & all the assumptions:

  • 1k row size table results: 6 minutes to read 1 table x 4 parallel processes (+ some merge execution overhead, lets assume we add 10 minutes to the process) = 16 minutes to read all 100m rows.  and if i’m not mistaken, 16 minutes is still faster than a single table scan of 26 minutes for 100m rows.  now, add a bit more overhead (+5 minutes for the increased parallel load): still adds up to 21 minutes, which is still less than a total run time of 26 minutes for all 100m rows.
  • 2k row size table results: 13 minutes to read 1 table x 4 parallel processes (+merge time, we’ll add 15 minute overhead to the process) + 8 minutes for process overhead of parallel query = 36 minutes.  which is still less than 52 minutes for 100m rows in the original table scan.
  • 4k row size table results: 26.041 minutes x 4 parallel processes (+ merge time, we’ll add 20 minute overhead to the process) + 10 minutes for parallel query processes = 56 minutes.  still less than 104.16 minutes for the table scan of the original table.
  • 8k row size table results: 52.083 m x 4 parallel processes (+ merge time, we’ll add 30 minute overhead to the process) + 20 minutes for parallel query processing = 102 minutes.  still less than 208 minutes for the table scan of the original table.

try this case yourself.  you *must* be accurate, and not execute a simple “select count(*)” that will not get you the results that you can measure against.  you must force a “select * from table”, and use joins on numeric columns in order to achieve these results.  the timing will vary depending on the hardware, infrastructure, disk, and database that are used.  however, joins & normalization (when setup properly) can only help performance when compared to table scans such as these.

i hereby maintain my claims for the data vault, and the normalization – that it is good for performance, and great for scalability – when tuned properly, and that it can & will beat denormalization and rows that get too wide along the way.

one more thing:

when you begin to “explode” the row size, or the table (regardless of satellite, or dimension, or fact, or otherwise) becomes too wide…  you increase the i/o’s needed to both read and write that same number of rows to disk.  the i/o’s will double, triple, and quadruple as you continue to widen the row – requiring double the i/o’s to get the data on and off disk.  the physical i/o count is tied directly to the performance of the table structure.  wider rows = more complexity = slower performance.  this has been proven over and over again in many different references below.

we also, “denormalize” to eliminate joins, but we denormalize key structures by using point-in-time, and bridge tables.  these are snapshot tables which house keys only, and provide “less joins” to get to the data in the data vault underneath.  however, it allows us to utilize the data vault structures to ensure flexibility, compatibility, and accountability going forward.

finally: over-normalization can take it’s toll.  today’s hardware (in rdbms systems – see above assumptions) cannot handle 6th normal form.   this is why columnar database engines with firmware and hardware adaptations are springing up.  over-normalize (add too many joins) and yes, performance will drop – but not from the normalization – but rather the amount of parallel load placed on the infrastructure underneath.  the same can be said for over-denormalizing.  make the rows too wide, and table scans begin to double / triple the i/o count (as shown here).  there is a fine-line between the two, and a sweet spot where they both perform equally well.

i hope this puts this “argument / complaint” about the data vault to bed once and for all.  if you still maintain that the data vault has too many joins after this article, then you do not understand the mathematics of parallelism, partitioning, and concurrency.

oh yea i almost forgot: the data vault is built for “machine based queries.” in other words, we do not need to tune the architecture for ad-hoc end user queries.  this means the architecture can and should be tuned for processes.  once tuned, it’s done!  no more maintenance (until the data set grows, and horizontal range partitioning needs to be added).  ad-hoc query tuning is a different beast, and has different requirements.  no, the data vault will not perform well under ad-hoc query access (in most cases).

leap of faith?

not really, it’s a proof of mathematics. i believe that “division of data”, both horizontal and vertical partitioning, assists division of tasks (multi-tasking / parallelism).  if the dependency on sequential read is not reduced, then there is an inherent upper limit to the scalability, size of data set, and performance that can be achieved (i am referring to straight table scans here vs using joins in parallel query operations).  horizontal partitioning is good for parallel queries, but people often forget that vertical partitioning (normalization) also helps.   yes, today there is such a thing as over-normalization.  too many joins / too much parallelism and the system can easily be overwhelmed.  when this is the case, the performance of the parallel threads slows down (not enough computing power to make use of the high degrees of parallelism)

but really, if the hardware uses normalization (division of tasks, division of data – partitioning), and continues to make advances with multi-threaded, parallel processing and performance gains, then why are we so hung up on table scans, sequential processing in the database world?  this just simply baffles me to no end.  the next question is: if this is truly the case (that non-parallelism scales better and faster than parallelism and normalization, then what are the rdbms vendors doing wrong to keep joins from performing in accordance with the parallel mathematics?)


in case you want to read more about parallelism, parallel query, degrees of parallelism, or in case you doubt my findings that joins and parallel tables (vertical partitioning) actually improves performance.  i believe that normalization enhances the optimizers’ ability to make decisions about parallelism – table scans leave the optimizers no choice but to execute sequential read-aheads in nearly all instances.

additional references for denormalization:

normalization linked to parallelism:

this is often ignored by those in the database world – all they talk about is how denormalization makes things faster.  yes, but only to a point – once that scalability point is reached, normalization and parallelism are the only way to scale further.  it’s why we have parallel os, multi-threaded cpu’s today, and why big smp systems cannot (today) out run mpp systems.  if the hardware uses normalization and parallelism, why can’t we take advantage of this at the database and data architecture levels?

Tags: , , , , , , , , ,

One Response to “Mathematics of Joins, Denormalization, Rows Per Block and IO”

  1. Dan Chatten 2011/09/15 at 6:03 pm #

    Good read, Dan !

Leave a Reply