Partitioned View Behavior - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Partitioned View Behavior

Author

Topic

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2006-10-09 : 03:06:08

Hi,

I am implementing horizontal partitioning on SQL Server 2000, I have created 5 tables with 6 million records each, proper constraints have been defined on each of them. I have created a view which UNION ALL these table, what i have noticed is I when select all records from a particular table through view it scans all tables for the first time and from then on it refers only one table wherenever i execute the same query.

If i modify the query to change where clause (add one more column in where clause) it againg refers all tables for the first time and from then on it refers only one table wherenever i execute the same query.

Can you tell me why this is happening?

thanks,
Hrishikesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 03:52:16

Are you checking the execution plan? or IO Statistics ?
Also, have you fulfilled all the requirements for partitioned view given in BOL?

Harsh Athalye
India.
"Nothing is Impossible"

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2006-10-09 : 05:11:23

Hi Harsh,

tkanks for the reply, yes all the requirement are met and yes i am using IO statistics. What is strange is why it accesses all the individual tables for the first time only.

tks,
Hrishikesh

quote:
Originally posted by harsh_athalye

Are you checking the execution plan? or IO Statistics ?
Also, have you fulfilled all the requirements for partitioned view given in BOL?

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 05:54:13

Can you post your view and query you are using to access data from view?

Harsh Athalye
India.
"Nothing is Impossible"

mr_mist
Grunnio

1870 Posts

Posted - 2006-10-09 : 06:09:43

The partitioned views can be incredibly random in their operation, I've found. Make sure you redo all the check constraints with the WITH CHECK option.

-------
Moo. :)

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2006-10-09 : 07:26:43

Hi,

Here is the script, please note that partitioned view works perfectly in 2005, if you run the same script.

CREATE TABLE [DBO].[GV_BP_VIOLATIONSUMMARY10] (
[DATAID] [INT] NULL ,
[VIOLATIONID] [INT] NULL ,
[RULEID] [INT] NULL ,
[CONDITIONID] [INT] NULL ,
[FunctionValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectID] [int] NULL ,
[ObjectTypeID] [int] NULL ,
[GroupId] [int] NULL ,
[ViolationSetGroupId] [int] NULL ,
[GroupExpressionID] [int] NULL ,
[BRConnectionID] [int] NULL ,
[ConditionPosition] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenericRuleDetailsId] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO GV_BP_ViolationSummary10
SELECT TOP 10 10,[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId] FROM GV_BP_ViolationSummary WHERE DATAID = 10

ALTER TABLE [dbo].[GV_BP_ViolationSummary10] WITH CHECK ADD CONSTRAINT CHECK_DATA_ID_10 CHECK(DATAID = 10)
ALTER TABLE [dbo].[GV_BP_ViolationSummary10] CHECK CONSTRAINT CHECK_DATA_ID_10

CREATE TABLE [dbo].[GV_BP_ViolationSummary11] (
[DataID] [int] NULL ,
[ViolationID] [int] NULL ,
[RuleID] [int] NULL ,
[ConditionID] [int] NULL ,
[FunctionValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectID] [int] NULL ,
[ObjectTypeID] [int] NULL ,
[GroupId] [int] NULL ,
[ViolationSetGroupId] [int] NULL ,
[GroupExpressionID] [int] NULL ,
[BRConnectionID] [int] NULL ,
[ConditionPosition] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenericRuleDetailsId] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO GV_BP_ViolationSummary11
SELECT TOP 10 11,[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId] FROM GV_BP_ViolationSummary WHERE DATAID = 10

ALTER TABLE [dbo].[GV_BP_ViolationSummary11] WITH CHECK ADD CONSTRAINT CHECK_DATA_ID_11 CHECK(DATAID = 11)
ALTER TABLE [dbo].[GV_BP_ViolationSummary11]CHECK CONSTRAINT CHECK_DATA_ID_11

CREATE TABLE [dbo].[GV_BP_ViolationSummary12] (
[DataID] [int] NULL ,
[ViolationID] [int] NULL ,
[RuleID] [int] NULL ,
[ConditionID] [int] NULL ,
[FunctionValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectID] [int] NULL ,
[ObjectTypeID] [int] NULL ,
[GroupId] [int] NULL ,
[ViolationSetGroupId] [int] NULL ,
[GroupExpressionID] [int] NULL ,
[BRConnectionID] [int] NULL ,
[ConditionPosition] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenericRuleDetailsId] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO GV_BP_ViolationSummary12 SELECT TOP 10 12,[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId] FROM GV_BP_ViolationSummary WHERE DATAID = 10

ALTER TABLE [dbo].[GV_BP_ViolationSummary12] WITH CHECK ADD CONSTRAINT CHECK_DATA_ID_12 CHECK(DATAID = 12)
ALTER TABLE [dbo].[GV_BP_ViolationSummary12] CHECK CONSTRAINT CHECK_DATA_ID_12

CREATE TABLE [dbo].[GV_BP_ViolationSummary13] (
[DataID] [int] NULL ,
[ViolationID] [int] NULL ,
[RuleID] [int] NULL ,
[ConditionID] [int] NULL ,
[FunctionValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectID] [int] NULL ,
[ObjectTypeID] [int] NULL ,
[GroupId] [int] NULL ,
[ViolationSetGroupId] [int] NULL ,
[GroupExpressionID] [int] NULL ,
[BRConnectionID] [int] NULL ,
[ConditionPosition] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenericRuleDetailsId] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO GV_BP_ViolationSummary13 SELECT TOP 10 13,[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId] FROM GV_BP_ViolationSummary WHERE DATAID = 10

ALTER TABLE [dbo].[GV_BP_ViolationSummary13] WITH CHECK ADD CONSTRAINT CHECK_DATA_ID_13 CHECK(DATAID = 13)
ALTER TABLE [dbo].[GV_BP_ViolationSummary13] CHECK CONSTRAINT CHECK_DATA_ID_13

CREATE TABLE [dbo].[GV_BP_ViolationSummary14] (
[DataID] [int] NULL ,
[ViolationID] [int] NULL ,
[RuleID] [int] NULL ,
[ConditionID] [int] NULL ,
[FunctionValue] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ObjectID] [int] NULL ,
[ObjectTypeID] [int] NULL ,
[GroupId] [int] NULL ,
[ViolationSetGroupId] [int] NULL ,
[GroupExpressionID] [int] NULL ,
[BRConnectionID] [int] NULL ,
[ConditionPosition] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[GenericRuleDetailsId] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO GV_BP_ViolationSummary14 SELECT TOP 10 14,[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId] FROM GV_BP_ViolationSummary WHERE DATAID = 10

ALTER TABLE [dbo].[GV_BP_ViolationSummary14] WITH CHECK ADD CONSTRAINT CHECK_DATA_ID_14 CHECK(DATAID = 14)
ALTER TABLE [dbo].[GV_BP_ViolationSummary14] CHECK CONSTRAINT CHECK_DATA_ID_14

CREATE VIEW VIEW_ViolationSummary
AS
SELECT [DataID],[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId]
FROM GV_BP_ViolationSummary10
UNION ALL
SELECT [DataID],[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId]
FROM GV_BP_ViolationSummary11
UNION ALL
SELECT [DataID],[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId]
FROM GV_BP_ViolationSummary12
UNION ALL
SELECT [DataID],[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId]
FROM GV_BP_ViolationSummary13
UNION ALL
SELECT [DataID],[ViolationID],[RuleID],[ConditionID],[FunctionValue],[ObjectID],[ObjectTypeID],[GroupId],[ViolationSetGroupId],[GroupExpressionID],[BRConnectionID],[ConditionPosition],[GenericRuleDetailsId]
FROM GV_BP_ViolationSummary14

SELECT OBJECTPROPERTY(ID,'CnstIsNotTrusted'), * FROM SYSOBJECTS WHERE NAME LIKE '%CHECK_DATA%'

--sp_refreshview VIEW_ViolationSummary
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT [ObjectID], [ObjectTypeID], [DataID] FROM VIEW_ViolationSummary WHERE DATAID = 10
SET STATISTICS TIME OFF
SET STATISTICS IO OFF

--

DROP VIEW VIEW_ViolationSummary
DROP TABLE GV_BP_VIOLATIONSUMMARY10
DROP TABLE GV_BP_VIOLATIONSUMMARY11
DROP TABLE GV_BP_VIOLATIONSUMMARY12
DROP TABLE GV_BP_VIOLATIONSUMMARY13
DROP TABLE GV_BP_VIOLATIONSUMMARY14

-- Here is the output for the first time
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(10 row(s) affected)

Table 'GV_BP_ViolationSummary14'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'GV_BP_ViolationSummary13'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'GV_BP_ViolationSummary12'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'GV_BP_ViolationSummary11'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.
Table 'GV_BP_VIOLATIONSUMMARY10'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

--------------Here is the next output--------------------

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.

(10 row(s) affected)

Table 'GV_BP_ViolationSummary14'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'GV_BP_ViolationSummary13'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'GV_BP_ViolationSummary12'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'GV_BP_ViolationSummary11'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0.
Table 'GV_BP_VIOLATIONSUMMARY10'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 07:51:04

One thing I notice is that partitioning key (DataID) is not part of Primary key. Also, it should not allow NULLs. Also before running second time, do you first execute:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

to clear execution plan from cache?

Harsh Athalye
India.
"Nothing is Impossible"

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2006-10-09 : 08:02:25

Hi Harsh,

This table doesn't have a primary key, i did change dataid to not null, and yes i tried
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

also i did update statistics on each table before exceuting query.

it is still accessing all tables.

thanks,
Hrishikesh

quote:
Originally posted by harsh_athalye

One thing I notice is that partitioning key (DataID) is not part of Primary key. Also, it should not allow NULLs. Also before running second time, do you first execute:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

to clear execution plan from cache?

Harsh Athalye
India.
"Nothing is Impossible"

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2006-10-09 : 09:30:37

Hi Harish,

Partitioned Column of a partitioned view SHOULD be part of primary key in 2000 while it is not so in 2005, thatz what is causing problem.

thanks,
Hrishikesh

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-09 : 09:36:58

Yes, that may be the problem. Can't you create a composite primary key to solve this problem?

Harsh Athalye
India.
"Nothing is Impossible"

hrishi_des
Yak Posting Veteran

76 Posts

Posted - 2006-10-10 : 01:15:47

Hi Harsh,

We wanted to develop a generic component for Partitioned View, so there could be a case where in no primary was defined on a table.

MSDN document says:

Partitioning columns must be a part of the primary key of the table.

so does it mean "IF primary key is present on table " then Partitioning columns must be a part of the primary key of the table Or Every table must have a primary key.

thanks,
Hrishikesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources