Time keeps on running and as I promised in my last article I would like to compare the Process ADD vs Partition Merge technique. In the previous blog I evaluated options for Process ADD command with respect to memory consumption, now I would like to compare it with using the “new partition method for each increment”.
How this method which you can use for new data add works:
- Add new empty partition with source query command which brings new data into table.
- Process partition
- Merge partition into one of the existing partitions.
As explained previously (and here as well), this method is most likely in the background of Process Add command. So, let’s review if there is any difference from using Process Add.
This is the behavior of CPU and memory consumption while using this technique:
- Create partition command – instant – zero memory impact
- New partition processing (100 rows) – Heavy CPU impact, duration 1:45 min, memory consumption 0.42 GB
- Partition Merge command – duration 5 sec
Partition commands duration summary:
| Phase | Duration |
| Create Partition: | 0 |
| Processing 100 records: | 1 min 45 sec |
| Merge Partition | 5 sec |
When we compare with ProcessAdd to the small partition recommendation from my last blog, we can see Partition merge is slightly slower.
| Memory min GB | Memory max GB | Delta GB | Duration (min) | |
| Part 3 (Small one) | 2.73 | 3.13 | 0.41 | 01:43 |
Summary
As an advantage of merge approach, I would highlight that you are sure about memory consumption! You always consume as small amount of memory as possible.
With Process ADD technique – small partition will grow in time = memory consumption grows. Therefore, you will create some new partition for this as well at some point.
However, for partition merge approach you (or the process user) need to be server admin as this is DB alter command! Which might be difficult to get in some corporate production environments so Process Add could be your no. 1 choice in this case.