Partitioned View Issue | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Partitioned View Issue

I have a partitioned view that when run using static values searches only the appropriate member table it should. However, when the static values are changed to variables and those variables assigned values, it (the query optimizer) searches all the member tables in the view, even though the sql itself is exactly the same. This is very easy to reproduce. The following script will create 2 small tables with the correct partitioning criteria, a view over the tables and then insert a few rows into each member table. Lastly, run the sql at the bottom and then check the plan produced by the query. You can see that all member tables are checked when variables are used instead of static values. This is not a big deal with a small # of rows in a table…however, the member tables we are dealing with have 2-6 million rows each so I don’t want the optimizer checking each one when I have already told it where to find the data. What’s the deal? Many thanks for any help with this. /***** Script start ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tst1]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[tst1]
GO CREATE TABLE [dbo].[tst1] (
[checkDate] [int] NOT NULL ,
[state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO ALTER TABLE [dbo].[tst1] WITH CHECK ADD
CONSTRAINT [PK_tst1] PRIMARY KEY CLUSTERED
(
[checkDate],
[state],
[city]
) ON [PRIMARY],
CONSTRAINT [CK_tst1] CHECK ([CheckDate] = 20021)
GO
/*************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tst2]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [dbo].[tst2]
GO CREATE TABLE [dbo].[tst2] (
[checkDate] [int] NOT NULL ,
[state] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[zip] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO ALTER TABLE [dbo].[tst2] WITH CHECK ADD
CONSTRAINT [PK_tst2] PRIMARY KEY CLUSTERED
(
[checkDate],
[state],
[city]
) ON [PRIMARY],
CONSTRAINT [CK_tst2] CHECK ([CheckDate] = 20022)
GO
/**************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[vuTst]’) and OBJECTPROPERTY(id, N’IsView’) = 1)
drop view [dbo].[vuTst]
GO SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO CREATE
View vuTst
AS
SELECT * FROM tst1
UNION ALL
SELECT * FROM tst2 GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/***************/
insert into tst2 values(20022,’colorado’,’denver’,’88277′)
insert into tst2 values(20022,’colorado’,’durango’,’88277′)
insert into tst2 values(20022,’colorado’,’leadville’,’88277′)
insert into tst2 values(20022,’colorado’,’vail’,’88277′)
insert into tst2 values(20022,’colorado’,’aspen’,’88277′)
insert into tst2 values(20022,’colorado’,’vista’,’88277′)
insert into tst2 values(20022,’colorado’,’col sprngs’,’88277′)
insert into tst2 values(20022,’texas’,’dallas’,’75207′)
insert into tst2 values(20022,’texas’,’ft. worth’,’76204′)
insert into tst2 values(20022,’texas’,’abilene’,’78277′)
insert into tst2 values(20022,’texas’,’amarillo’,’74907′)
insert into tst2 values(20022,’texas’,’houston’,’88307′)
/****************************/
insert into tst1 values(20021,’colorado’,’denver’,’88277′)
insert into tst1 values(20021,’colorado’,’durango’,’88277′)
insert into tst1 values(20021,’colorado’,’leadville’,’88277′)
insert into tst1 values(20021,’colorado’,’vail’,’88277′)
insert into tst1 values(20021,’colorado’,’aspen’,’88277′)
insert into tst1 values(20021,’colorado’,’vista’,’88277′)
insert into tst1 values(20021,’colorado’,’col sprngs’,’88277′)
insert into tst1 values(20021,’texas’,’dallas’,’75207′)
insert into tst1 values(20021,’texas’,’ft. worth’,’76204′)
insert into tst1 values(20021,’texas’,’abilene’,’78277′)
insert into tst1 values(20021,’texas’,’amarillo’,’74907′)
insert into tst1 values(20021,’texas’,’houston’,’88307′)
insert into tst1 values(20021,’texas’,’austin’,’88307′)
insert into tst1 values(20021,’texas’,’el paso’,’88307′)
insert into tst1 values(20021,’texas’,’san anton’,’88307′)
/***** Script end ******/ declare @state varchar(10)
declare @checkdate int
declare @city varchar(10)
set @city = ‘dallas’
set @checkdate = 20021
set @state = ‘texas’
select * from vuTst
where checkDate = 20021 [email protected]
and state = ‘texas’ [email protected]
and city = ‘austin’ [email protected] /* this sql checks for untrusted partitioning columns */
SELECT name, status FROM sysobjects WHERE xtype = ‘C’ AND status &0x800 = 0x800
What is the ‘lazy schema validation’ set to?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Not sure…but I will check. Why would this have any effect? Isn’t that option primarily for remote servers? This view is entirely local. Thanks.
Yes, this is for remote servers, as I understand it. I assumed that this was your case, as the benefits of using partioned views on a local SQL Server aren’t a whole lot? What benefits do expect to get from using local partioned views?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
What I hope for is this: imagine 10 tables each with 5 million rows in them.
tbl200201, tbl200202, etc. Each table has exactly the same definition and each has a column defined as the "partitioning column". I build a view of the syntax:
select * from tbl200201
UNION ALL
select * from tbl200202
UNION ALL
etc. I then select from the view instead of the underlying member table and give the optimizer enough information (by use of the partitioning column) to know which specific member table contains the data I’m looking for. The optimizer identifies the correct member table and issues the SQL against it rather than against EACH member table. This is how Microsoft describes the use of partitioned views. I think the reasoning is that a select against 5 million rows will always be faster than a select against 50 million.
What I’m finding is that when using static values to issue the select, it works as advertised. The plan shows that the optimizer correctly identified the proper member table and issued the select against it *ONLY*. When using variables however, the plan generated shows that the optimizer checked all member tables in search of the data. It doesn’t make sense. PS. The performance difference in this small test is inconsequential. When you’re looking through 50 million rows it is definitely noticeable. Thanks again for your help.

What you describe sounds reasonable, but I don’t have any personal experience with it, so I can’t offer any solutions now. Has anyone else done this before? If I have time, I will try to research this topic and see what I can find.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
http://www.sqlmag.com/forums/messageview.cfm?catid=12&threadid=7904 The above link contained an article dealing with this issue. It looks like the optimizer is choosing the correct table after all, though only at runtime. At SQL-parse time, a literal constant can be evaluated immediately and the correct table chosen with no further effort. However when a variable is used it is evaluated only at runtime…thus the 2 plans. I am going to verify that this is the case.
Please let us know, this is very interesting.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>