Post reply

Partitioned View

  • January 15, 2010 at 12:25 pm

    #218288

    Hi,

    We have a very big table in our OLTP database (SQL Server 2008) has about 40 million records. We want to optimize the data access by making use of partitioned views. We are planning to split this one big table into multiple tables of 3 or 4 million records each. Selects and updates are going to be fine based on a unique ID. But how do we know what table to insert to? In other words how do we determine the table the records to be inserted to?

    Is this even possible? if anyone has come across this situation, please share the ideas/solutions.

    Much appreciated.

    Thanks in Advance.

    Amol Naik

  • January 15, 2010 at 12:40 pm

    #1104406

    The first step is to know the structure of the table. Oh, we don't. Can you post the DDL (CREATE TABLE statement) for the table?

    Second step, what edition of SQL Server 2008 are you running, Standard or Enterprise?

  • January 15, 2010 at 12:43 pm

    #1104407

    The table is a name-value pair table has only 3 columns:

    [ID] -- NOT an identity, but a FK to another table in the db.

    [Name]

    [Value]

    Primary key is [Object_ID] and [Name].

    We are on SQL Server 2008 Enterprise Edition.

    Thanks!

    Amol Naik

  • January 15, 2010 at 12:49 pm

    #1104409

    One, SQL Server 2008 supports partitioned tables, you may want to read about them in BOL (Books Online, the SQL Server Help System).

    Two, how is the ID determined?

    Three, good luck. I'm not sure how you would partition a EAV table. Sounds like a difficult process. Using a partitioned table, once you figure out how you are going to accomplish the partitioning will be easier than a partitioned view as SQL Server will handle the inserting of records into the appropriate partition for you.

  • January 15, 2010 at 12:56 pm

    #1104412

    Thanks for the response Lynn. I agree with you, i am aware of partitioning although i wanted to see if i could effectively use partitioned views in this scenario.

    I guess i would go with Table Partitioning then.

    Amol Naik

  • January 15, 2010 at 7:07 pm

    #1104524

    interesting post man

    Bienville House New Orleans

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

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