Is it a good practice to apply Vertical Partition to a newly created model?

Question

Suppose you are modeling the ER model for a new Object Oriented Web Application.

There is a table with 20 columns, and there are going to be a lot of INSERTs populating 10 columns, and a lot of UPDATEs populating the remaning columns.

The amount of INSERT and UPDATE statements on this table may reach thounds per second.

Since there are a lot of locks occuring all the time (caused by the INSERTs and UPDATEs), there is a concern that performance may be a problem, since response time has to be a reasonable one for a Web Application.

Someone suggests that the table should be split into 2 other tables: One containing the columns that are inserted first, and the other one contaning the remaning columns, that would be updated.

Keep in mind that this would require a more complex Object-relational mapping, since the OO Model and all the documents consider it to be 1 single entity, not 2.

My questions:

• Is it a good suggestion? Would you consider it? Is there a better way of doing it? is it going to help at all?

or

• Is this manly a hardware problem? Should I model my application database ignoring this kind of optimization and focusing on getting the entities right? Should I just tell the hardware guys that I need better I/O?

Please let me know if any information is missing and thanks in advance.

Answer 1

Yes, it is a valid physical design modification to accommodate more flexible locking behavior.

If you join the two tables and expose it to your ORM with a view, I don't think the ORM will know the difference (unless it attempts to look at the view metadata like looking for primary key or something during code generation).

Your view should be updatable, and even if it isn't due to some peculiarity of updatable view limitations can always be made updatable using INSTEAD OF triggers.

And yes, I would certainly consider it. Whether I would consider it before actually doing load testing on a single table design, I'm not sure.

Here's an example in SQL Server:

CREATE TABLE main
( ID INT IDENTITY NOT NULL
 ,DATA VARCHAR(255)
 ,CONSTRAINT PK_MAIN PRIMARY KEY CLUSTERED (ID ASC)
 );

CREATE TABLE aux
( ID INT NOT NULL
 ,MOREDATA VARCHAR(255)
 ,CONSTRAINT PK_AUX PRIMARY KEY CLUSTERED (ID ASC)
 );

GO

CREATE VIEW unified
AS
  SELECT main.*, aux.MOREDATA
  FROM main
  INNER JOIN aux
    ON aux.ID = main.ID;
GO

INSERT INTO main (DATA) VALUES ('somedata');

INSERT INTO aux (ID, MOREDATA) VALUES (SCOPE_IDENTITY(), 'some more data');

Then:

BEGIN TRANSACTION;

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;

SELECT *
FROM unified;

UPDATE unified
SET MOREDATA = 'changed data'
WHERE ID = 1;

SELECT *
FROM unified;

SELECT dm_tran_locks.request_session_id,
       dm_tran_locks.resource_database_id,
       DB_NAME(dm_tran_locks.resource_database_id) AS dbname,
       CASE
           WHEN resource_type = 'object'
               THEN OBJECT_NAME(dm_tran_locks.resource_associated_entity_id)
           ELSE OBJECT_NAME(partitions.OBJECT_ID)
       END AS ObjectName,
       partitions.index_id,
       indexes.name AS index_name,
       dm_tran_locks.resource_type,
       dm_tran_locks.resource_description,
       dm_tran_locks.resource_associated_entity_id,
       dm_tran_locks.request_mode,
       dm_tran_locks.request_status
FROM sys.dm_tran_locks
LEFT JOIN sys.partitions ON partitions.hobt_id = dm_tran_locks.resource_associated_entity_id
LEFT JOIN sys.indexes ON indexes.OBJECT_ID = partitions.OBJECT_ID AND indexes.index_id = partitions.index_id
WHERE resource_associated_entity_id > 0
  AND resource_database_id = DB_ID()
ORDER BY request_session_id, resource_associated_entity_id;

COMMIT TRANSACTION;

Answer 2

I believe what you are asking above is actually more table design. Partitioning in DB2 has a very specific meaning. However, given that you are worried about performance, it is apropos to discuss DB2 partitioning and what it means.

To start with there are two types of partitioning in DB2:

• Table Partitioning
• Database Paritioning

There is also another form of partitioning known as Multidimensional Cluster Tables (MDCs). More on MDCs later.

Table Partitioning allows you to divide a table internal to DB2. It will store data grouped/divided by a particular partitioning scheme. A common way to partition is based on some date. For example you may partition based on year. Essentially this is like having the same table definition over and over again (though not really), but each "table" has only the data related to that particular partition key. Indexes written over table partitioned tables, get "split" in the same way. This allows for great flexibility. Depending on the data you are querying/updating, DB2 will optimize and only visit the indexes and table partitions that fall within the ranges of your data in question. This essentially means less I/O (thus less CPU and memory). You get table partitioning for "free" within the Enterprise Edition of DB2. From what I understand you can choose to use table partitioning after a table is created. You just have to alter the table and tell it to re-distribute the data (which is a cost to time, resources, and locks) but it is do-able.

Database Partitioning is similar, but more along the lines of having load balanced servers. You can declare a database partition group across multiple servers. When you create a table you specify which partition group (or even partitions) to place it in. And once again, you specify a partition key. This will split the table up between the physical servers (or database partitions). And once again, DB2 will optimize your query/update based on the criteria in your SQL. It will know on which server (or servers) your data is residing on. This allows for a spread of CPU, memory, and I/O utilization across several physical servers. (Again, think load balancing here.). Note that table must be declared in a partition group if you want to use this (otherwise I believe it defaults to the partition you are currently working on and it will only place the table on that partition). But the benefit of placing it in a group is that as you add/remove partitions, you can tell DB2 to re-partition the data and it will spread it out accordingly based on what is available to it (though again, re-distribution is a cost). Database partitioning is only available via the Database Partitioning Feature (aka DPF). Though included with the binaries for DB2 Enterprise Edition, it is a separate license that needs to be purchased to enable this.

Multidimensional Clustering Tables (MDCs) allow for another form of "partitioning" data. They allow you to build data using a block cluster index structure (say clustering on sales territory). DB2 will create the table and specify a huge chunk of disk as "blocks" and write data related to the clustering key to that block. Updates are technically less expensive than a traditional index as it does not need to know the RID of the location on disk. It only needs to the know the block ID that the data is stored in. This allows for very fast efficient retrieval of lots of data. Because of this, MDCs are most often used in data warehouses and data marts (though they can also be used in OLTP environments) as they return larger amounts of data through less I/O. The key here is clustering on the right columns (unlike traditional indexes where you want high cardinality, you actually want lower cardinality for MDCs to insure less I/O as you want to retrieve larger "chunks" of data). Note that a table MUST be declared as an MDC up front. You can't change a table to MDC after it is created. MDCs are available for "free" in Enterprise Edition.

The nice thing about each of these three features is that they are independent of each other and thus you can use each. For example, I could create a MDC clustered on sales territory, table partition it based on year, and then place it in a database partition group (partitioned on hash, ie an internal hashing id mechanism DB2 uses) across all database partitions.

Now depending on my query, only the CPUs, memory, and disk necessary will be used to retrieve my data. This can free up resources for more work as each piece is doled out only to the necessary hardware components.

Now, with what I all said, realize that you don't just want to do all three. That would probably be pre-optimization, which is usually a bad thing. It would be best to try out each option individually first to see what you gain, and then maybe also different combinations. Doing this will take time, but in essence helps to tune your database and engine. Take your time on it. (also note again, that DPF requires a license fee). If you do figure out that say you need table partitioning and maybe want to move to database partitioning later, it may be a good idea to define a table with table partitioning and then define it in a database partition group (even if that group has only one partition). Then when you add another database partition (server) to the group, you can tell DB2 to redistribute the data and pick up the benefits of the other server. Thus scaling out that way.

But again, don't just do these things for the sake of doing them. Take them into account with table design changes and test these in other environments under load conditions, etc. so you understand both the complexity of setting them up as well as the benefits of each.

Answer 3

Breaking up the 20 columns into 2 tables of 10 columns each may be a good approach from database design standpoint. Let's call this segregation, or perhaps functional segregation, since we seem to be separating them based on some functional cohesion.

The object mapping overhead this may cause in your application architecture is not my specialty, but since we know that most business application software is more bound by I/O than by CPU, it would seem better to focus on the I/O, as long performance is the more important factor.

Perhaps it would make sense to segregate the attributes into two different object classes, since there seems to be some behavioral difference? Would this alleviate some of your object mapping overhead?

---

Note: for those who are unfamiliar with horizontal vs vertical partitioning, please see [Wikipedia]