Partitioned Tables/Views and Query Variables

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 Tables/Views and Query Variables

Author

Topic

joshms
Starting Member

5 Posts

Posted - 2005-05-11 : 13:58:55

I'm new to these forums, but it seems I've been experiencing a lot of the same problems as many others in respects to MS's partitioning feature in SQL Server 2000 (SP4).

I’ve currently got 3 tables set up, partitioned on an int year column. (2003, 2004, 2005)

After a tedious battle, I’ve had some success in getting the query engine to utilize the proper execution plans for a partitioned set up. Statements like:


select top 100 percent * from vPartTables where db_year = 2003

seem to work well.

The problem comes into play when I attempt to create stored procedures designed to work with the partitioned view. Most specifically it occurs when you assign a variable to the where clause condition for the partition column. Consider the statement above, but restructured as the following:


declare @yr int
set @yr = 2003

select top 100 percent * from vPartTables where db_year = @yr

This new structure does not execute using the appropriate execution plan for partitioned tables (it dips into all three source tables, instead of just the 2003 table.)

Obliviously, you can see the dilemma this causes when I try to create some stored procedures against this partition.

The closest I have been able to come is to write the whole stored procedure as a long @sql nvarchar string, concatenating the appropriate variable values into their correct positions within the where clause and then passing the resulting @sql string to sp_executesql.


declare @yr int
set @yr = 2003

declare @sql nvarchar(4000)
set @sql = ‘select top 100 percent * from vPartTables where db_year=’ + convert(nvarchar, @yr)

exec sp_executesql @sql

This seems like a huge HACK, and some of the things I need to do to make this work (like creating temp tables instead of imbedded subqueries) seem to do more harm than good. Not to mention, sp_executesql is not available on the sql function level – so I’m at a total loss there.

Appreciate any help,

Josh

mr_mist
Grunnio

1870 Posts

Posted - 2005-05-12 : 05:27:41

To be honest, I was never able to get them to work properly either. The underlying plan almost always looked at the individual tables, regardless of most parameters, especially if any GROUP operators were used and such.

You may see an improvement by removing and recreating your constraints with the WITH CHECK option, then remaking the views.

-------
Moo. :)

joshms
Starting Member

5 Posts

Posted - 2005-05-12 : 11:32:07

Yeah, I noticed during my previous efforts to get partitioning working at all, that remaking the constraints and checking "Check existing data on creation" was one key to success.

What’s so frustrating is that I'm so close (I can actually produce optimized SQL queries) but it doesn't serve much practical purpose for my application unless I can do it in a stored procedure - which inherently use parameters...

-Josh

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-05-12 : 11:43:00

This was an interesting read. Maybe this explains the problem you are seeing.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_sa_5r1v.asp

EDIT:
can you post an execution plan for both the sproc and the dynamic sql?

-ec

joshms
Starting Member

5 Posts

Posted - 2005-05-12 : 15:27:30

The following will produce the partitioned table/view...


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VOrders]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VOrders]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Orders1998]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Orders1998]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Orders1999]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Orders1999]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[Orders2000]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Orders2000]
GO

CREATE TABLE [Orders1998] (
	[OrderID] [int] NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[OrderDate] [datetime] NULL ,
	[OrderYear] [int] NOT NULL ,
	CONSTRAINT [OrderIDYear98] PRIMARY KEY  CLUSTERED 
	(
		[OrderID],
		[OrderYear]
	)  ON [PRIMARY] ,
	 CHECK (datepart(year,[OrderDate]) = 1998),
	 CHECK ([OrderYear] = 1998)
) ON [PRIMARY]
GO

CREATE TABLE [Orders1999] (
	[OrderID] [int] NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[OrderDate] [datetime] NULL ,
	[OrderYear] [int] NOT NULL ,
	CONSTRAINT [OrderIDYear99] PRIMARY KEY  CLUSTERED 
	(
		[OrderID],
		[OrderYear]
	)  ON [PRIMARY] ,
	 CHECK (datepart(year,[OrderDate]) = 1999),
	 CHECK ([OrderYear] = 1999)
) ON [PRIMARY]
GO

CREATE TABLE [Orders2000] (
	[OrderID] [int] NOT NULL ,
	[CustomerID] [int] NOT NULL ,
	[OrderDate] [datetime] NULL ,
	[OrderYear] [int] NOT NULL ,
	CONSTRAINT [OrderIDYear00] PRIMARY KEY  CLUSTERED 
	(
		[OrderID],
		[OrderYear]
	)  ON [PRIMARY] ,
	 CHECK (datepart(year,[OrderDate]) = 2000),
	 CHECK ([OrderYear] = 2000)
) ON [PRIMARY]
GO

CREATE   VIEW VOrders
AS

SELECT * FROM dbo.Orders1998
UNION ALL
SELECT * FROM dbo.Orders1999
UNION ALL
SELECT * FROM dbo.Orders2000


select top 100 percent * from vOrders where OrderYear=1999

Query Plan
|--Compute Scalar(DEFINE:([Orders1999].[OrderID]=[Orders1999].[OrderID], [Orders1999].[CustomerID]=[Orders1999].[CustomerID], [Orders1999].[OrderDate]=[Orders1999].[OrderDate], [Orders1999].[OrderYear]=[Orders1999].[OrderYear]))
|--Clustered Index Scan(OBJECT:([JOSH_TEST].[dbo].[Orders1999].[OrderIDYear99]))


DECLARE @yr int
SET @yr = 1999

select top 100 percent * from vOrders where OrderYear=@yr

Query Plan
|--Concatenation
|--Filter(WHERE:(STARTUP EXPR([@yr]=1998)))
| |--Clustered Index Scan(OBJECT:([JOSH_TEST].[dbo].[Orders1998].[OrderIDYear98]), WHERE:([Orders1998].[OrderYear]=[@yr]))
|--Filter(WHERE:(STARTUP EXPR([@yr]=1999)))
| |--Clustered Index Scan(OBJECT:([JOSH_TEST].[dbo].[Orders1999].[OrderIDYear99]), WHERE:([Orders1999].[OrderYear]=[@yr]))
|--Filter(WHERE:(STARTUP EXPR([@yr]=2000)))
|--Clustered Index Scan(OBJECT:([JOSH_TEST].[dbo].[Orders2000].[OrderIDYear00]), WHERE:([Orders2000].[OrderYear]=[@yr]))

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-12 : 16:05:24

Even though OrderYear is a constant for each table, does adding an index to that column help any? I would expect that it might, since it wouldn't require a scan to determine which table(s) do not have values for that year.

- Jeff

joshms
Starting Member

5 Posts

Posted - 2005-05-12 : 16:13:44

I'm not sure if this is for better or worse, but it actually added a bookmark lookup when I created that new index on all 3 tables. (OrderYear Asc)

Execution Plan

|--Concatenation
|--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([JOSH_TEST].[dbo].[Orders1998]))
| |--Filter(WHERE:(STARTUP EXPR([@yr]=1998)))
| |--Index Seek(OBJECT:([JOSH_TEST].[dbo].[Orders1998].[IX_OrdersYear1998]), SEEK:([Orders1998].[OrderYear]=[@yr]) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1002]), OBJECT:([JOSH_TEST].[dbo].[Orders1999]))
| |--Filter(WHERE:(STARTUP EXPR([@yr]=1999)))
| |--Index Seek(OBJECT:([JOSH_TEST].[dbo].[Orders1999].[IX_OrdersYear1999]), SEEK:([Orders1999].[OrderYear]=[@yr]) ORDERED FORWARD)
|--Bookmark Lookup(BOOKMARK:([Bmk1008]), OBJECT:([JOSH_TEST].[dbo].[Orders2000]))
|--Filter(WHERE:(STARTUP EXPR([@yr]=2000)))
|--Index Seek(OBJECT:([JOSH_TEST].[dbo].[Orders2000].[IX_OrdersYear2000]), SEEK:([Orders2000].[OrderYear]=[@yr]) ORDERED FORWARD)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-05-12 : 17:29:00

The extra index won't do squat..
Th optimser looks only at the CHECK constraint for determining the table to query..

As the article states, the optimiser cannot know for sure if the variable is in the check constraint range. So it throws its hands in the air and says.. "stuff it, I'll look at them all". Useless!

Even when literals are used, there seems to some predicates (BETWEEN, >, etc) that just refuse to make use of the the correct tables..

Have they fixed this 2005?

DavidM

A front-end is something that tries to violate a back-end.

joshms
Starting Member

5 Posts

Posted - 2005-05-12 : 18:33:11

I've successfully implemented the partitioning with literals using all sorts of operators (including between and in). My last hurdle is with these variables. I did notice that the filter's descriptions do seem to be describing what the article states - so maybe this is as good as it gets for now?

I've done some playing around with SQL 2005 April CTP however they only let you play with the "express" version - which does not support partitioning. However, from what I gather, partitioning is taken to a whole new level 2005. Rather than views and base tables, you write a "Partition Function" and apply it to the desired partition column of just one table. I've also seen screen shots of the enterprise edition in which shows an "is partitioned" property on a table object - something which seems a lot more concrete than the current implementation where you just hope you've done all you can to help SQL Server figure out that your data is partitioned.

-Josh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources