Post reply

Partitioned Table

  • May 20, 2009 at 1:02 pm

    #134435

    Hi,

    I partitioned an existing table and i don't see any performance. My original table has around 13Million records.. It has 1 clustered primary key (ID, Date) and 2 non-clustered indexes.. 1) BoxID 2) Date, ID

    i did the follow:

    Create my filegroup (FG1)

    Create my partition function pf_date

    Create my partition scheme ps_date

    I move the data/table into the new scheme like:

    ALTER TABLE [table_name] DROP CONSTRAINT PK_ WITH (MOVE TO [ps_date] (Date))

    GO

    ALTER TABLE [table_name] ADD CONSTRAINT PK_ PRIMARY KEY(ID, Date)

    GO

    I checked the partition data and its located in my filegroup and in each partition..

    I run some queries to see the difference in time, but Im getting the same result...

    Any advice??

  • May 20, 2009 at 1:07 pm

    #997842

    What do your queries look like? Could you post one or two of them?

  • May 20, 2009 at 1:12 pm

    #997846

    I did a basic one like:

    select * from [table_name] where date > '2009-03-01 00:00:00.000'

    i tired on my production DB where the table is not partitioned yet and I tried on my development DB where the table is partitioned and I have the same result (in time)

  • May 20, 2009 at 1:15 pm

    #997848

    lily1706 (5/20/2009)

    I did a basic one like:

    select * from [table_name] where date > '2009-03-01 00:00:00.000'

    i tired on my production DB where the table is not partitioned yet and I tried on my development DB where the table is partitioned and I have the same result (in time)

    This query can't use the clustered index. If you were to swap your Id and Date in the clustered index, I think you will see a difference.

  • May 20, 2009 at 1:23 pm

    #997853

    I did, I created a table with the clustered index like date, ID..

    i didnt work either...

    do you think cause i move the actual data to the partition scheme can affect this? or i sd create a new table and then insert into the new table?? I need to use the same table_name for my queries...

  • May 20, 2009 at 1:33 pm

    #997860

    I don't know. You did not provide the actual code for the partition scheme, partition function, or the partition table.

  • May 20, 2009 at 1:44 pm

    #997865

    PARTITION FUNCTION

    CREATE PARTITION FUNCTION pf_NAME (datetime)

    AS RANGE RIGHT

    FOR VALUES

    (

    '2008-01-01 00:00:00.000'

    ,'2008-02-01 00:00:00.000'

    ,'2008-03-01 00:00:00.000'

    ,'2008-04-01 00:00:00.000'

    ,'2008-05-01 00:00:00.000'

    ,'2008-06-01 00:00:00.000'

    ,'2008-07-01 00:00:00.000'

    ,'2008-08-01 00:00:00.000'

    ,'2008-09-01 00:00:00.000'

    ,'2008-10-01 00:00:00.000'

    ,'2008-11-01 00:00:00.000'

    ,'2008-12-01 00:00:00.000'

    ,'2009-01-01 00:00:00.000'

    ,'2009-02-01 00:00:00.000'

    ,'2009-03-01 00:00:00.000'

    ,'2009-04-01 00:00:00.000'

    ,'2009-05-01 00:00:00.000'

    ,'2009-06-01 00:00:00.000'

    ,'2009-07-01 00:00:00.000'

    ,'2009-08-01 00:00:00.000'

    ,'2009-09-01 00:00:00.000'

    ,'2009-10-01 00:00:00.000'

    ,'2009-11-01 00:00:00.000'

    ,'2009-12-01 00:00:00.000'

    )

    PARTITION SCHEME

    CREATE PARTITION SCHEME Ps_NAME

    AS PARTITION pf_NAME

    ALL TO (FG1)

    MOVE DATA TO NEW SCHEME

    ALTER TABLE [table_name] DROP CONSTRAINT PK_ WITH (MOVE TO [ps_name] (Date))

    GO

    ALTER TABLE [table_name] ADD CONSTRAINT PK_ PRIMARY KEY(ID, Date)

    GO

  • May 20, 2009 at 2:03 pm

    #997877

    What you are trying to accomplish may not benefit from a partitioned table. What is your objective? How much data do you have? What are the normal queries that are run against this table?

  • May 20, 2009 at 2:15 pm

    #997886

    What you are trying to accomplish may not benefit from a partitioned table. What is your objective? How much data do you have? What are the normal queries that are run against this table?

    I just want to get data faster when I query old information (date), its for a report.. I have around 80Millions of records in one table...

    I can query just the specific table or i can join this table with other one (this one is not partitioned)

    note: Im grouping the data in only one Filegroup, but i guess this make sense.. Should I create more than one filegroup???

    What is your advice???

  • May 20, 2009 at 2:26 pm

    #997895

    What do your typical queries look like, not the one you alread showed me by the way.

  • May 20, 2009 at 2:57 pm

    #997918

    these 2:

    selectdistinct t1.Date, t2.MacAddress

    fromTABLE1 t1

    joinTABLE2 stb on t2.Id = t1.Id

    wheret1.Date between '2009-03-01' and '2009-03-30'

    selectt1.MacAddress, t2.date, t1.Id

    fromdbo.Table1 t1

    joindbo.Table2 t2 on t2.Id = t1.Id

    WHEREt1.Id not in

    (select t2.Id

    from Table2 t2

    where t2.date >= '2009-03-01' )

  • May 20, 2009 at 3:10 pm

    #997929

    First, i think the first query should benefit from the partioned table. Not sure about the second one.

    Second, I took the time to rewrite your queries. I think these may be a bit more effecient, but you'll have to test them to see if they work for you.

    select distinct

    t1.Date,

    t2.MacAddress

    from

    TABLE1 t1

    inner join TABLE2 stb

    on t2.Id = t1.Id

    where

    t1.Date >= '2009-03-01' and

    t1.date = '2009-03-01')

    WHERE

    t2.id is null

  • May 20, 2009 at 3:33 pm

    #997956

    Thank you for your advice..

    Im getting the same time, but the second query help me to get the info a little faster but, with less information...

    anyway!! I guess the partition is not good for this case..

    Thank you again

Viewing 13 posts - 1 through 12 (of 12 total)

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