Better performance with partitioned views

Introduction

When there are tables with large number of records, performance is a concern. If a table with large number of records can be divided in to set of smaller tables, a better performance can be expected when accessing individual tables.
But since there are several tables, it is not possible to access all the tables as a single object from a application. To overcome this problem, partitioned view is a better solution.

Partitioned view
Partitioned view combines results sets taken from different tables with UNION ALL statement and a CHECK constraint should be used on all underlying tables to separate the range of data (on a selected column) between each table.

Let's see how this can be done with a simple example

Create two tables

CREATE TABLE [dbo].[Invoice_1](
[InvoiceID] [int] CONSTRAINT PK1 PRIMARY KEY NOT NULL,
[Description] [varchar] (200))
GO

CREATE TABLE [dbo].[Invoice_2](
[InvoiceID] [int] CONSTRAINT PK2 PRIMARY KEY NOT NULL,
[Description] [varchar] (200)
)
GO

Create the view

CREATE VIEW vw_Invoice

AS

SELECT [InvoiceID] ,[Description]

FROM [dbo].[Invoice_1]

UNION ALL

SELECT [InvoiceID] ,[Description]

FROM [dbo].[Invoice_2]

Insert two records to the two tables

INSERT INTO Invoice_1 ([InvoiceID],[Description])

VALUES (1,'LogicalPartitioning Test')

INSERT INTO Invoice_2 ([InvoiceID],[Description])

VALUES (1100,'LogicalPartitioning Test2')

Execute a SELECT statement against the view and see how the execution plan

SELECT *

FROM vw_Invoice

WHERE InvoiceID = 1

Even though the query returns only one records which is selected from the first table, the SQL server access both the tables.

Now add two constraints to the two tables and see how the execution plan changes

ALTER TABLE Invoice_1 ADD CONSTRAINT C1 CHECK (InvoiceID BETWEEN 1 AND 1000)

ALTER TABLE Invoice_2 ADD CONSTRAINT C2 CHECK (InvoiceID BETWEEN 1001 AND 2000)

Execute a SELECT statement again and see how the execution plan now

SELECT *

FROM vw_Invoice

WHERE InvoiceID = 1

Now this clearly shows that the execution plan uses only the first table when executing the same query. If the number of tables is higher, the benefit will be much higher.

Highlights

• The ranges used in the constraint should not be overlapped between underlying tables
• If the partitioned column is part of the PRIMARY KEY, data can directly be inserted in to the view