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

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.

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;

StackExchange Database Administrators Q#31459, answer score: 6