Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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
 

Print | posted on Monday, September 24, 2007 8:44 PM |

Feedback

Gravatar

# re: Horizontal partitioning

the beauty of partitioned views :)
9/24/2007 9:03 PM | Mladen
Gravatar

# re: Horizontal partitioning

I'm surprised that horizontal partitioning would be considered for such a small table.
9/25/2007 8:19 PM | Tara
Gravatar

# 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.
9/25/2007 9:39 PM | Peso
Gravatar

# re: Horizontal partitioning

is it necessary to have PK on the partitioned key
9/27/2007 9:35 PM | Kapil Gupta
Gravatar

# re: Horizontal partitioning

Yes. The primary key must exist of

1) The unique column
2) The check constraint
10/2/2007 1:54 PM | Peter Larsson
Gravatar

# 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)?
4/15/2010 9:14 PM | Bill
Gravatar

# re: Horizontal partitioning

No, the order of key columns doesn't matter for functionality.
For performance, the order may matter.
4/15/2010 10:08 PM | Peso
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET