Horizontal partitioning
Today I investigated the potential benefits of horizontal partitioning for a client using SQL Server 2000.
I profiled the client's queries to the database and found out that current quarter only is used in 72% of the queries, 21% of queries is current and previous quarter only and remaining 7% of queries is for any quarter.
Their current Order table has about 4.5 million records, and the average duration for a query is about 1200 ms. Every quarter has an average of 100000 records (about 11 years of data).
With this information at hand, I decided to partition the current order table in three;
· Current quarter
· Previous quarter
· All other quarters
After creating the partitioning scheme in development area, the current quarter queries averaged at an exciting 112 ms, current and previous quarter averaged at 243 ms and average duration for all queries dropped to 1000 ms.
One thing happened during testing that I was not prepared for. I thought I was going to get an error when trying to update an order so that the check-constraint got violated. It didn't. Instead, SQL Server moved the record to the proper table!
With this new information, I can implement the horizontal partitioning seamlessly. Rename Order table to OrderCurr, create a new table OrderPrev and a third table OrderHist. I am going to create a partitioned view named Order, which means all code will continue to function.
For demonstration only, this is how I proceeded.
CREATE TABLE Orders2000
(
OrderID INT NOT NULL,
CustomerID VARCHAR(15) NOT NULL,
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Orders2000
PRIMARY KEY(
OrderDate,
OrderID
),
CONSTRAINT UQ_Orders2000_OrderID
UNIQUE (
OrderID
),
CONSTRAINT CHK_Orders2000_OrderDate
CHECK (
OrderDate >= '20000101'
AND OrderDate < '20010101' )
)
CREATE TABLE Orders2001
(
OrderID INT NOT NULL,
CustomerID VARCHAR(15) NOT NULL,
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Orders2001
PRIMARY KEY(
OrderDate,
OrderID
),
CONSTRAINT UQ_Orders2001_OrderID
UNIQUE (
OrderID
),
CONSTRAINT CHK_Orders2001_OrderDate
CHECK (
OrderDate >= '20010101'
AND OrderDate < '20020101'
)
)
CREATE TABLE Orders2002
(
OrderID INT NOT NULL,
CustomerID VARCHAR(15) NOT NULL,
OrderDate DATETIME NOT NULL,
CONSTRAINT PK_Orders2002
PRIMARY KEY(
OrderDate,
OrderID
),
CONSTRAINT UQ_Orders2002_OrderID
UNIQUE (
OrderID
),
CONSTRAINT CHK_Orders2002_OrderDate
CHECK (
OrderDate >= '20020101'
AND OrderDate < '20030101'
)
)
I then created a partitioned view like this
CREATE VIEW vwAllOrders
AS
SELECT OrderID, CustomerID, OrderDate FROM Orders2000
UNION ALL
SELECT OrderID, CustomerID, OrderDate FROM Orders2001
UNION ALL
SELECT OrderID, CustomerID, OrderDate FROM Orders2002
I inserted some test data
INSERT vwAllOrders
(
OrderID,
CustomerID,
OrderDate
)
SELECT 1,
'Peso',
'20011225'
UNION ALL
SELECT 2,
'Jennie',
'20020314'
For verification, I run this query
SELECT 'Orders2000 before update' AS Action, OrderID, CustomerID, OrderDate FROM Orders2000
UNION ALL
SELECT 'Orders2001 before update', OrderID, CustomerID, OrderDate FROM Orders2001
UNION ALL
SELECT 'Orders2002 before update', OrderID, CustomerID, OrderDate FROM Orders2002
Where I got this result
Action OrderID CustomerID OrderDate
Orders2001 before update 1 Peso 2001-12-25
Orders2002 before update 2 Jennie 2002-03-14
Now I updated one of the orders
UPDATE vwAllOrders
SET OrderDate = '20000906'
WHERE OrderID = 1
And ran this query for verification
SELECT 'Orders2000 after update' AS Action, OrderID, CustomerID, OrderDate FROM Orders2000
UNION ALL
SELECT 'Orders2001 after update', OrderID, CustomerID, OrderDate FROM Orders2001
UNION ALL
SELECT 'Orders2002 after update', OrderID, CustomerID, OrderDate FROM Orders2002
And got this result
Action OrderID CustomerID OrderDate
Orders2000 after update 1 Peso 2000-09-06
Orders2002 after update 2 Jennie 2002-03-14
The order 1 has moved to proper table according to constraint without manual intervention!
If you are interested, check out the execution plans for this query
SELECT * FROM vwAllOrders
Legacy Comments
|
Mladen
2007-09-24 |
re: Horizontal partitioning the beauty of partitioned views :) |
|
Tara
2007-09-25 |
re: Horizontal partitioning I'm surprised that horizontal partitioning would be considered for such a small table. |
|
Peso
2007-09-25 |
re: Horizontal partitioning It is not that the table has 19 columns of UNIQUEIDENTIFIER datatype. It is that the table is queried (during the 1.5 hour measurement period) for 52000 times. If I can reduce the average time for the query from 1.2 seconds to 0.1 seconds, there is much time saved during the day. |
|
Kapil Gupta
2007-09-27 |
re: Horizontal partitioning is it necessary to have PK on the partitioned key |
|
Peter Larsson
2007-10-02 |
re: Horizontal partitioning Yes. The primary key must exist of 1) The unique column 2) The check constraint |
|
Bill
2010-04-15 |
re: Horizontal partitioning Does the order of the columns in the primary key matter? I mean, can I put the column involved in the check constraint after the unique column(s)? |
|
Peso
2010-04-15 |
re: Horizontal partitioning No, the order of key columns doesn't matter for functionality. For performance, the order may matter. |