Wednesday, March 21, 2012

Partitioned View performance

What is wrong with my partitioned view?
I have split a table into partitioned view by month (see below).
Check the following query information
--this is the original table
select count(*) from activitydetailbackup
where [datetime] between '1/2/2006' and '1/26/2006'
--takes 35 seconds
--this is run against the view
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
--takes over 6 minutes
SET STATISTICS IO ON
select count(*) from activitydetailbackup_view
where [datetime] between '1/2/2006' and '1/26/2006'
SET STATISTICS IO OFF
--shows the following
Table 'ActivityDetailBackup200603'. Scan count 4, logical reads 76860,
physical reads 0, read-ahead reads 76865.
Table 'ActivityDetailBackup200602'. Scan count 4, logical reads 2366,
physical reads 0, read-ahead reads 2365.
Table 'ActivityDetailBackup200601'. Scan count 4, logical reads 73249,
physical reads 0, read-ahead reads 73250.
Table 'ActivityDetailBackup200512'. Scan count 4, logical reads 42978,
physical reads 67, read-ahead reads 42930.
Table 'ActivityDetailBackup200511'. Scan count 4, logical reads 44662,
physical reads 67, read-ahead reads 44631.
Table 'ActivityDetailBackup200510'. Scan count 4, logical reads 41996,
physical reads 0, read-ahead reads 41996.
Table 'ActivityDetailBackup200509'. Scan count 4, logical reads 36542,
physical reads 0, read-ahead reads 36546.
Table 'ActivityDetailBackup200508'. Scan count 4, logical reads 41171,
physical reads 0, read-ahead reads 41175.
Table 'ActivityDetailBackup200507'. Scan count 4, logical reads 38037,
physical reads 66, read-ahead reads 38269.
Table 'ActivityDetailBackup200506'. Scan count 4, logical reads 38804,
physical reads 65, read-ahead reads 39051.
Table 'ActivityDetailBackup200505'. Scan count 4, logical reads 40052,
physical reads 70, read-ahead reads 40332.
Table 'ActivityDetailBackup200504'. Scan count 4, logical reads 37436,
physical reads 70, read-ahead reads 37693.
Table 'ActivityDetailBackup200503'. Scan count 4, logical reads 38750,
physical reads 66, read-ahead reads 38890.
Table 'ActivityDetailBackup200502'. Scan count 4, logical reads 32304,
physical reads 73, read-ahead reads 32451.
Table 'ActivityDetailBackup200412'. Scan count 4, logical reads 33051,
physical reads 72, read-ahead reads 33131.
Table 'ActivityDetailBackup200411'. Scan count 4, logical reads 36257,
physical reads 69, read-ahead reads 36429.
Table 'ActivityDetailBackup200410'. Scan count 4, logical reads 24012,
physical reads 69, read-ahead reads 24149.
Table 'ActivityDetailBackup200409'. Scan count 4, logical reads 32, physical
reads 1, read-ahead reads 31.
Each table is created as follows:
CREATE TABLE [dbo].[ActivityDetailBackupYYYYMM](
[ActivityID] [uniqueidentifier] NOT NULL,
[DateTime] [datetime] NOT NULL,
[PageName] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Querystring] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FormVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SessionVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ServerVariables] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomValue] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [ActivityIDDateYYYYMM] PRIMARY KEY CLUSTERED
(
[ActivityID] ASC,
[DateTime] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
ALTER TABLE [dbo].[ActivityDetailBackup200410] WITH NOCHECK ADD CHECK
(([DateTime] >= convert(datetime,'10/1/2004') and [DateTime] <
convert(datetime,'11/1/2004')))
View created as follows:
CREATE VIEW [dbo].[ActivityDetailBackup_view]
AS
SELECT * FROM ActivityDetailBackup200409
UNION ALL
SELECT * FROM ActivityDetailBackup200410
UNION ALL
SELECT * FROM ActivityDetailBackup200411
UNION ALL
SELECT * FROM ActivityDetailBackup200412
UNION ALL
SELECT * FROM ActivityDetailBackup200502
UNION ALL
SELECT * FROM ActivityDetailBackup200503
UNION ALL
SELECT * FROM ActivityDetailBackup200504
UNION ALL
SELECT * FROM ActivityDetailBackup200505
UNION ALL
SELECT * FROM ActivityDetailBackup200506
UNION ALL
SELECT * FROM ActivityDetailBackup200507
UNION ALL
SELECT * FROM ActivityDetailBackup200508
UNION ALL
SELECT * FROM ActivityDetailBackup200509
UNION ALL
SELECT * FROM ActivityDetailBackup200510
UNION ALL
SELECT * FROM ActivityDetailBackup200511
UNION ALL
SELECT * FROM ActivityDetailBackup200512
UNION ALL
SELECT * FROM ActivityDetailBackup200601
UNION ALL
SELECT * FROM ActivityDetailBackup200602
UNION ALL
SELECT * FROM ActivityDetailBackup200603
UNION ALL
SELECT * FROM ActivityDetailBackup200604
Haroldsthe reason might be because of adding the constraint with no check..
so it has to go and check the existing data in all the tables to find out if
rows exists.|||My understanding of the NOCHECK in the ALTER TABLE statement just means to
set the constraint without validating the data currently on the table.
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.|||your understanding is right. So when you select from the view, it cannot rel
y
on the contraint you have created and has to go and check all the tables.
This is what I believe. If you can try this. create the constraints (without
the no check option and run it
"Harolds" wrote:
> My understanding of the NOCHECK in the ALTER TABLE statement just means to
> set the constraint without validating the data currently on the table.
> --
> Harolds
>
> "Omnibuzz" wrote:
>|||This did the trick.
Thanks for the help,
--
Harolds
"Omnibuzz" wrote:

> the reason might be because of adding the constraint with no check..
> so it has to go and check the existing data in all the tables to find out
if
> rows exists.

No comments:

Post a Comment