Partition switching when you can have old data

Question

Partition switching when you can have old data

kyagi.jo

SSCrazy

Points: 2559
More actions
September 4, 2015 at 7:38 am

#320997

Hi,
I am thinking of migrating one of my databases and implementing partitions, and using partition switching to load data quickly, so I'm reading up about it.
For the most part, it fits well, and I should be able to partition most of the data by a date key, but there will be cases where we do need to load data from the staging tables that is already loaded into a partition, and I am not sure about how that would be dealt with.
Because the partition is already in use, I would want to do something like copy the partition that the data belongs in the partition, load the data into the newly copied partition, and then switch out the old partition, and then switch in the new partition. Is something like this possible? I don't see any function to copy a partition in this way. The alternative using partition switching would be to switch the partition out, load it, and put it back in, but the period where the partition is missing is going to be significant, if I don't have something in the partition slot in the meantime. Is there any good solution to this, or do I need to load the partitions without partition switching?

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

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

BrainDonor SSCoach Points: 19252 More actions · Answer 1

kyagi.jo (9/4/2015)
Hi,
I am thinking of migrating one of my databases and implementing partitions, and using partition switching to load data quickly, so I'm reading up about it.
For the most part, it fits well, and I should be able to partition most of the data by a date key, but there will be cases where we do need to load data from the staging tables that is already loaded into a partition, and I am not sure about how that would be dealt with.
Because the partition is already in use, I would want to do something like copy the partition that the data belongs in the partition, load the data into the newly copied partition, and then switch out the old partition, and then switch in the new partition. Is something like this possible? I don't see any function to copy a partition in this way. The alternative using partition switching would be to switch the partition out, load it, and put it back in, but the period where the partition is missing is going to be significant, if I don't have something in the partition slot in the meantime. Is there any good solution to this, or do I need to load the partitions without partition switching?

It is indeed possible and I've used that in the past. I needed to reload data into a live database that was effectively parameter information. The data was out of date and needed replacing. The table was already partitioned and all of the data resided in one partition (Partition A, for the sake of argument) and we had another partition for switching and purging (Partition B). I created a new partition (C) and loaded the new data into that. Switched A to B and then C to A. Partition A has the updated details and B could be purged, removing the old data.

If you struggle with it, I should be able to dig out the code and demonstrate it.

Steve Hall
Linkedin
Blog Site

kyagi.jo SSCrazy Points: 2559 More actions · Answer 2

Hi,

That's fine, it makes sense, but my exact case, I view it as more like if the initial partition is A, then I want to partition switch, but load (A + B) as the replacement partition to A, rather than a different C that isn't based on A. If there was some function that could quickly clone A, so that I could load what is effectively a copy of A with B, and then switch it in, then this would be good, as there isn't too much data in relation to the size of A, to be added, so I don't want to take too long making the actual partition, but still want to take advantage of partition switching, if I can.

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

kyagi.jo (9/4/2015)
Hi,
That's fine, it makes sense, but my exact case, I view it as more like if the initial partition is A, then I want to partition switch, but load (A + B) as the replacement partition to A, rather than a different C that isn't based on A. If there was some function that could quickly clone A, so that I could load what is effectively a copy of A with B, and then switch it in, then this would be good, as there isn't too much data in relation to the size of A, to be added, so I don't want to take too long making the actual partition, but still want to take advantage of partition switching, if I can.

there will be cases where we do need to load data from the staging tables that is already loaded into a partition

First, how many rows in those staging tables.

Shifting gears, the idea of "cloning" a partition would probably take longer than just doing inserts from the staging 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)

kyagi.jo SSCrazy Points: 2559 More actions · Answer 4

It'll vary, but for some of the cases, we might be doing something like loading a customer's historic data, which might be on the scale of hundreds of thousands of rows.

Ok I think that the best answer would be to do the inserts for anything on the left of my sliding window, rather than to figure out how to copy and switch the partitions, as there's no quick "mirror this file" kind of functionality built into SQL server, and I would likely have to load the partition using conventional means.