topic Re: Very large fact table divided in very large QVDs: is it better a implicit concatenate or an explicit concatenate? in App Development

Very large fact table divided in very large QVDs: is it better a implicit concatenate or an explicit concatenate?

p_zaccheddu — Tue, 15 Apr 2025 12:25:56 GMT

Hypothetical case. Imagine a very big fact table (more than a billion rows). The data is in QVDs divided by years. For example:

FACT_2015.qvd
FACT_2016.qvd
FACT_2017.qvd
etc

Each qvd has about 20 fields and the average size is 200 MB.

To load them we have two methods
First an implicit concatenate (pseudo code):

FACT:
load * from FACT_*.qvd

or an explicit concatenate (always pseudo code)

set Firstyear=2015
set LastYear=2025

for i=$(Firstyear) to $(LastYear)
if i=$(Firstyear) then
set vTable='FACT:'
else
set vTable='Concatenate (FACT)'
end if

$(vTable)
load * from FACT_$(i).qvd

In terms of loading speed, would the two methods be equivalent or is one better than the other? I don't have data of this size at the moment, so I wanted to know if anyone had already done some tests in this sense 🙂

Re: Very large fact table divided in very large QVDs: is it better a implicit concatenate or an explicit concatenate?

marcus_sommer — Tue, 15 Apr 2025 14:17:35 GMT

I would expect no significantly difference in the run-times because it's always a loop-approach regardless if it's performed with an implicit syntax or an explicit ones. The iterations and/or the variable-evaluating should not be noticeable against the loads itself.

Personally I prefer the explicit approach but I wouldn't take this for-loop and also not setting the concatenate-statement else using:

for each file in filelist('FullPathWithPattern')
t: load F1, F2, F3, ... from [$(file)] (qvd);
next

Most important will be to ensure that the loads are optimized which means all files have the identically data-structure and no transformations are applied - unless a single where exists() is allowed.

Re: Very large fact table divided in very large QVDs: is it better a implicit concatenate or an explicit concatenate?

p_zaccheddu — Wed, 16 Apr 2025 09:07:47 GMT

Thanks. My other curiosity is that implicit concatenation obviously only works if there are the same fields on all QVDs. So I suppose that QS must do this check before saying "Ok, they are the same, let's concatenate them!". If I explicitly use the prefix "concatenate", wouldn't it be like telling QS "Ok, don't worry, concatenate them without doing checks"? I don't know if this leads to an actual time saving, I'm thinking of situations with a lot of QVDs or similar.

Re: Very large fact table divided in very large QVDs: is it better a implicit concatenate or an explicit concatenate?

marcus_sommer — Wed, 16 Apr 2025 09:50:11 GMT

Without specifying an explicit concatenate(MyTable) statement only tables with the identical data-structure are merged. If the tables have a different data-structure you will mandatory need the explicit statement. But different data-structures will break the optimized load - and this should be avoided by larger data-set.