Re: urgent help: view of partitioned tables

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: JJ Wang (anonymous_at_discussions.microsoft.com)
Date: 06/22/04 

Date: Tue, 22 Jun 2004 13:52:21 -0700

hi Steve,

I have a historical data set table of over 500 million
rows with no primary key on it (I inheritated it this
way!). I vertically partitioned the table into every
month by the date column.

this table has a clustered key on 'user' column (int), and
another index on two other columns (market, date). the
partitioned tables have the same indexes defined.

my origianl view is like this, and it's smoking fast for
the same query:

create view dbo.table_name_vu
as
select * from active_data_table
union all
select * from history_data_table

my new view is like this, and it's 25 times slower than
the old view:

create view dbo.table_name_vu_new
as
select * from active_data_table
union all
select * from history_data_table_month1
union all
.
.
union all
select * from history_data_table_month25

my test query:
select distinct date from table_name_vu_new
--(or table_name_vu)
where cdate = '5/4/2004'

it almost seems like my query time slowed down 25 times
when I have 25 partitioned tables versus 1 table.

should I build same index on the view to solve the problem?

many thanks.

JJ

>-----Original Message-----
>JJ,
>
> If the view meets the requirements for a partitioned
view, if the indexes
>are appropriate for the search, your search should be at
least as fast as
>searching each table individually. If the search filter
is on the
>partitioning column and the results are in one table, the
search should be
>as fast as searching that table alone. Without seeing
your table and view
>definitions, index definitions, and and example of a
query that runs slowly,
>it's impossible to guess what's wrong. Can you provide
them?
>
>Steve Kass
>Drew University
>
>
>
>"JJ Wang" <anonymous@discussions.microsoft.com> wrote in
message
>news:1fc2601c457fd$0ff9ad60$a001280a@phx.gbl...
>> hi,
>>
>> I have a view that 'union all' bunch of partitioned
tables
>> of over 30 million rows each. these tables have over
500
>> million rows in total.
>>
>> each table has clustered index and a combined index (of
>> two columns) on them. When you search each individual
>> tables, it's really fast.
>>
>> Yet, if you search the same query through the view, it
>> takes forever!
>>
>> we had a view of one whole big table instead of
>> partitioned union (same index built), it was lightening
>> fast;
>>
>> how come with the table partitioned, the view is soooo
>> slow?
>>
>> many helps!
>>
>> JJ
>
>
>.
>

Relevant Pages

  • Partitioned View not behaving as expected: Scanning ALL partitions instead of those in the WHERE cl
    ... UNION ALL ... I have included a CHECK CONSTRAINT on the Partitioning column for EACH of the paritions to include ONLY the dates for that particular month: ... I'm seeing that the query plan is scanning ALL of the tables in the view! ... Member tables are defined in the FROM clause in each SELECT statement in the view definition. ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL UNION
    ... If you have a partitioning column that can be ... select id,name from t1 union all ... I cannot use this query for indexed ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL UNION
    ... If you have a partitioning column that can be ... select id,name from t1 union all ... I cannot use this query for indexed ...
    (microsoft.public.sqlserver.programming)
  • Re: urgent help: view of partitioned tables
    ... rows with no primary key on it (I inheritated it this ... the same query: ... union all ... >partitioning column and the results are in one table, ...
    (microsoft.public.sqlserver.tools)
  • Re: urgent help: view of partitioned tables
    ... rows with no primary key on it (I inheritated it this ... the same query: ... union all ... >partitioning column and the results are in one table, ...
    (microsoft.public.sqlserver.programming)