September 19, 2013 at 4:11 am
Hello there,
Does SQL Server 2008 R2 support Sub partitions?
Thanks
September 19, 2013 at 4:25 am
What do you mean by 'sub partitions'? What are you looking to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2013 at 4:35 am
I have a big table which is currently partitioned on a column called SupplierName. We get new data every week, for each supplier, and inserting data into this massive table takes a long time. Currently, we drop indexes, load data and then recreate indexes.
If I could further partition data under each Supplier then I could just switch partitions instead of loading data
September 19, 2013 at 4:56 am
Ah, no.
That said, sounds like you might want to re-do the partitioning on the table and instead partition on date rather than supplier. Then you have your fast load and fast delete (if necessary) based on time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2013 at 5:21 am
Thanks.
Will there be any performance degradation since most queries first go with Supplier and then date? In other words, do you see any chance of table level locks?
September 19, 2013 at 6:20 am
Partitioning isn't about performance anyway. It's about maintainability, fast loads, etc. You may need to change nonclustered indexes, or you may not. Can't tell without a lot more information.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 19, 2013 at 11:51 am
Thanks for your responses. They were very helpful
September 19, 2013 at 4:15 pm
sf.onlineforums (9/19/2013)
Will there be any performance degradation since most queries first go with Supplier and then date? In other words, do you see any chance of table level locks?
That's indeed a valid concern. If your queries typically go by supplier disregarding the data, you will need to have indexes on supplier which is not partitioned-aligned. since else the data for the supplier is spread out all over the partitions, and performance will suffer.
Then again, if you are not loading or dropping data by supplier, I am not sure that there is much point of partitioning by supplier at all.
if you have a limited number of supplieries, one option would be to construct a an artifical key which is a combination of supplier and date, and now you can have the sub-partitions you desired. But you will need to modify you queries to use a range on this artificial key to query for a certain supplier.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 21, 2013 at 2:47 am
Thanks Erland.
Loading data by date, every week, is the main reason for redesigning partitions to be based on dates. I like your idea of generating an artificial key. Do you recommend partitioning the table on artificial key or just creating indexes would do the same thing?
September 21, 2013 at 2:53 am
Welcome to 'it depends' 🙂
If you want to do fast loads and fast deletes (switch partitions in/out), then partition. If you want to do index maintenance at the partition level, then partition. If not, then probably no need to partition
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2013 at 4:42 am
sf.onlineforums (9/21/2013)
Loading data by date, every week, is the main reason for redesigning partitions to be based on dates. I like your idea of generating an artificial key. Do you recommend partitioning the table on artificial key or just creating indexes would do the same thing?
I don't recommend anything, because I know too little of your system and your requirements. I'm just tossing up ideas. They may be right, or they may be a complete disaster.
What I can say is that using an artificial key will make your system more complex and add maintenance costs, and may also be a burden to users, if they access the database and will have to work with this key. But if this solution is needed to get acceptable performance for both loading and querying data, you may be prepared to pay the price.
And, oh, speaking about tossing up ideas, SQL Server is able to work with partitioning in two levels, since there are both partitioned tables and partitioned views. You could have one table per supplier, each table partioned by date. Then you would have a view which unites the supplier tables, so it looks like a single table. I need to add the caveat that I have no idea how well the optimizer is able to handle the combination. And, again this is a complex solution which will increase the total cost of ownership for the system.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 21, 2013 at 6:08 am
Fair enough.
I'm going to partition the table by date and use partition switching to expedite the process of loading data. The only caveat is that I have to drop the non-clustered index (supplier), before switching partitions as it's not aligned
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply