Split Original Huge data into multiple ones.

[lfalmoguera]

Hi all, 

thanks a lot in advance for the help.

I am looking forward a more efficient way to "allocate" data from a huge data table into other more little ones.

I have +1000M registers table and I want to split it into other more little NoConcatenate ones.

For example. I have this table:

month	field1	value
201901	A	A1
201902	A	A2
201903	A	A3
201904	A	A4
201905	A	A5
201906	A	A6
201907	A	A7
201908	A	A8
201909	A	A9
201910	A	A10
201911	A	A11
201912	A	A12
201901	B	B1
201902	B	B2
201903	B	B3
201904	B	B4
201905	B	B5
201906	B	B6
201907	B	B7
201908	B	B8
201909	B	B9
201910	B	B10
201911	B	B11
201912	B	B12

Imagine I want to split the data into as many tables as combinations between month&field1

Is there any way to do it straightforward without doing a loop with different where conditions?

It consumes too mucho time by iterating the original table every loop.

Thanks!

[marcus_sommer · Accepted solution]

I think there is no way to avoid a loop with appropriate where-statements but it doesn't need to happens with the origin table else you could pull the whole table (and if no usable key is available because it should be a from several fields combined one - you should create that key as this point) and store it as qvd and then using the qvd to loop through by applying a where exists(YOURKEY) to ensure that they are loaded in an optimized mode and then storing your slices.

Before doing it I suggest to consider if it's really suitable to create for each period + other field an own table. Of course there are various benefits to slice bigger datasets but if it ends in hundreds or even thousands of rather small tables you may get more and more disadvantages because the overhead to the table-handling may increase significantly and might be higher than the pure data-loading.

- Marcus

[Brett_Bleess]

Did Marcus' post get you what you needed?  If so, do not forget to close out the thread by using the Accept as Solution button on his post.  If you did something different, consider posting that and then use the button to mark that as the solution.  If you are still working on things, leave an update.

Regards,
Brett

[lfalmoguera]

Thanks a lot for the answer Marcus!