I creared partitioned view, i see performance gain for reading data but performance degrades for my inserts.

asomar 7/28/2004 5:33:59 PM

I created a partitioned view on 12 tables, one for each month. The primary key of each table is (Date, id) and is clustered. I created a check constraint on date for each table. I did some tests to check the execution time of the same query over the table with data of the whole year and the partitioned view created. For my surprise the second was much bigger! Checking the execution plan of the query over the view it makes a index seek for all the tables, including the ones with date out of the between range. Is ir right? How to make sure The view I created is considered partitioned?

marcelo9/21/2002 1:04:54 AM

Hi,
My name is Lucy. I've read your article about creating horizontally partitioned views (article dated April 2000 in SQL Server magazine).
I working with an application which has a similar problem- large table, but user retrieves only small amount of data. Of course performance is horrible. Partition views would be definitely a solution for our intent to improve performance. Your article was really helpful, but it leaves a few questions unanswered. I've tried to search for an answer on web, but could not find anything. You would be a real help if you just take a few minutes and answer on two of my questions.

1. What is the maximum number of tables can be used in single horizontally partitioned view?

2. Does horizontally partitioned view technique works with queries where inner join is used for specifying the criteria?
Ex:
select * from vwJobAnalysis inner join tblPeriod on vwJobAnalysis.per_end_dt =tblPeriod.per_end_dt

It seems that SQL Server scans all tables even when only one table has a data that matches the criteria in table tblPeriod. (I've created a few tables, all tables are have the same structure and no indexes. Each table has a constraint on field "per_end_dt" equals to specific period). SQL Server scans only one table if a change my syntax to :

SELECT * FROM vwJobAnalysis WHERE per_end_dt IN ("08/15/2001")

Please let me know if I did something wrong.
-Thanks in advance.
Lucy Khovyeva.

LUCY KHOVYEVA 8/20/2001 8:49:24 PM

Where can i Fined Listing 1 and Listing 2 ??

Zeev5/2/2001 3:55:20 PM

Do horizontally partitioned views work with a large number of tables? Say, over 100? I created the view with just 10 tables and defined contraints on the tables and the results were considerably slower than when I was querying an individual table. I need to find out if I am missing something.

david freibrun 8/28/2000 8:11:06 AM