Partition for a particular value and not

Question

Partition for a particular value and not

Anamika

SSCertifiable

Points: 6202
More actions
March 23, 2015 at 9:10 pm

#316352

hi,
How can I make partitions on a table for a particular value and ranges together?
For example, for customer id 12345 i need a separate partition, then for 56789 i need a separate partition, and if i have range of values like 1000 to 1020 then a separate partition for this.
For certain ids i need unique partition, and for certain ids i need Ranges.
is it possible in SQL 2012?
Thanks and Regards,
Ami

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

Anamika (3/23/2015)
hi,
How can I make partitions on a table for a particular value and ranges together?
For example, for customer id 12345 i need a separate partition, then for 56789 i need a separate partition, and if i have range of values like 1000 to 1020 then a separate partition for this.
For certain ids i need unique partition, and for certain ids i need Ranges.
is it possible in SQL 2012?
Thanks and Regards,
Ami

You say you need the partitions. What is that need because that will determine 1) if you really should partition at all, 2) the key that you actually need for the partitions, and 3) the storage mechanism for each partition.

I'll tell you up front that partitioning is not a panacea for performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anamika SSCertifiable Points: 6202 More actions · Answer 2

Hi Jeff,

Thanks for you reply.

i'll explain the scenario, so that we can decide on whether we need partition or not.

1. It is a datawarehouse system and we have 500+ millions of records and expected to grow 6-8 times.

2. ETLs happens thrice in a day and one schedule will bring around 3-4 millions of data everyday.

3. The columns are customer code, date column, material code and value.

Here the material code is vast and they are connected with customer. But i cannot fit range of material code into customers though they are serial. When we partition on date wise then the recent date will be queried always and it'll lead to performance issues...

your comments and advice are highly appreciated.

Thanks and Regards,

Ami

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

1. Is any of that data ever updated once it's been inserted into the table? If so, what is updated column-wise?

2. What are the current indexes that you have on the table?

3. Do you have an FKs that point at this table?

4. This is [font="Arial Black"]one [/font]table with 500 million rows, correct?

5. What do you normally query by in this table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anamika SSCertifiable Points: 6202 More actions · Answer 4

Hi Jeff,

I'm sorry for a delayed reply. Thanks for your help. Below are my answers.

1. Is any of that data ever updated once it's been inserted into the table? If so, what is updated column-wise?

It doesn't get updated.

2. What are the current indexes that you have on the table?

Composite PK on Customer Code / Material ID / Date and Time / Site of that customer

MaterialId and dateTime ( ignore dup key is on to avoid failures while inserting from various sources. Though all four contribute to PK - if these two keys are real contributors)

3. Do you have an FKs that point at this table?

From Time dimension

From Material dimension

From site (Customer and site together referred from this table)

4. This is one table with 500 million rows, correct?

Yes

5. What do you normally query by in this table?

Material Id and DateTime

Thanks and Regards,

Ami

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

To answer your original question, yes, it is possible to partition based on the CustomerID as 1 partition for some customers and 1 partition for ranges of customers. Just keep in mind that the ranges of customers cannot contain the customers that you want individual partitions for.

Shifting gears a bit and based on your answers to my questions, (Items 1, 4, and 5 are really important here), I'd probably [font="Arial Black"]NOT [/font]partition by CustomerID. Rather, I'd partition by the DateTime column for all the right reasons to partition. If you (for example) partition using separate filegroups for each month (for example), you'll get even more benefit.

1. If you partition by month (for example) and each partition lives on a single file in a separate filegroup for each month, the you can set "previous months" to read only and stop backing up the entire table. If you (for example) have 5 years of data in the table, this will drop your backups to anywhere from nearly 0 minutes at the beginning of a month to 1/60th (5 years *12 months = 60) of the time it currently takes to back the table up.

2. Index maintenance periods will drop in a similar fashion because you won't need to reindex the older partitions even if they weren't set to read only.

3. Online Piecemeal restores become a possibility.

Some of the queries that are based on DateTime ranges [font="Arial Black"]may [/font] enjoy some performance improvement due to partition elimination. I stress "may" because partitioning frequently does not provide performance benefits for queries and isn't a good reason to partition.

If you intend to "drop" customers from the table, especially on a regular basis, then a different partitioning approach might be in order.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Anamika SSCertifiable Points: 6202 More actions · Answer 6

Thanks Jeff...

your inputs are highly contributing to our decision makings in partitions...

Thanks and Regards,

Ami

jadebarton85 Newbie Points: 2 More actions · Answer 7

You want at everyone thespians keep your portions around the same remember it's metastasized maximum a protein and face eyes maximum of carbohydrates and speaking of carbohydrates car tonight after p.m. if you find that you go to bed around on Striction BP[/url] around o'clock at night so we told you all have mine but I go straight up my workout but June Eve men on just eat the fibrous crabs so instead of having a complex like your eyes for you Tate is acting like that happy bitch doubles see abruptly cauliflower mushrooms peppers stuff like that and this'll help you maintain all those great results that you've worked.

For more information please contact us at --> > >> > > > http://www.optimalstackfacts.org/striction-bp-the-best-supplement/