creating a view for one big table

Question

creating a view for one big table

apat

SSCertifiable

Points: 5929
More actions
April 27, 2010 at 11:42 am

#223938

Hey All,
I have one huge table with billions of records. Just curious, If I separate data from that table in 10 tables and create clustered index on all the 10 tables and then create a view for all those tables to fetch data from my SP from front end. I can also created indexed view if needed.
Will this help improve performance at all?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

In SQL Server 2005/2008, you can have SQL Server manage your partitions. You don't need to create individual tables and join them with a view. As far as improving performance, the answer is yes, as long as the partition key is used in the query. For example, if you partition by a date column, you just want to be sure that you use the date column in your WHERE clause. This allows the query optimizer to only touch the partitions that hold the data for that date.

Here's[/url] a great article on the topic. It's quite long but well worth the read.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

apat SSCertifiable Points: 5929 More actions · Answer 2

Thanks for the info John,

I am not partitioning the table and creating any partition function and range etc. All I am doing is just separating data from one HUGE table to 10 different tables and fetching them by a view instead of quering that table directly. unfortunately I can't change my SP to put one more filter to fetch data from that partition etc...

So, will just creating view like this and separating tables will help? shall I create indexed view or just an index on all the tables will work?

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

Yes, you can index a partitioned view. There are some requirements, like all tables must be in the same database and there must be a unique clustered index, which you can think of as the primary key on the view. Once you have the unique clustered index, you can add additional indexes as well. In SQL Server Standard edition, you usually have to use a special NOEXPAND hint to get the query optimizer to use the index.

http://msdn.microsoft.com/en-us/library/dd171921.aspx

Also, the following article talks about partition elimination (only reading from the required tables) with partitioned views.

http://msdn.microsoft.com/en-us/library/aa175850(SQL.80).aspx

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Stefan_G SSCertifiable Points: 6609 More actions · Answer 4

apat (4/27/2010)
Hey All,
I have one huge table with billions of records. Just curious, If I separate data from that table in 10 tables and create clustered index on all the 10 tables and then create a view for all those tables to fetch data from my SP from front end. I can also created indexed view if needed.
Will this help improve performance at all?

In my opinion it is unlikely that just splitting the table will give any performance improvements.

It all depends on the table, indexes and query.

Can you post the table definition, the query you are trying to improve and the actual execution plan as an .sqlplan file ?

/SG