Are there any known limitations for intervalmatch() ?
Are there any known limitations or bugs for intervalmatch()?
I am trying to match about 3.200.000 intervals with 26.280 timestamps (3 years, 24 hour / day).
During reload the script makes progress for the first 10 minutes and then gets stuck around 492 million rows, which is roughly around 50% of the estimated result. After that it doesn't do anything for 3+ hours (at which moment we killed the reload). The QVD it should store never shows up. It also does a short "freeze" around 210 million rows (for around 15 seconds), but the customer says "the server does that sometimes during reloads".
Currently running June 2020 SP1. Server has 128GB RAM (but doesn't even get close to 50% usage).
I don't know how the intervalmatch is technically performed but it's a rather heavy transformation and like similar simplified load-statements like the crosstable load or the generic load they aren't recommended by large datasets. Therefore I'm not really surprised that they may stuck by any limitation or needing much time to finish. A quite classical approach in such cases would be to slice the task in n smaller parts and to concatenate them afterwards.
Beside this I don't use the intervalmatch (the logic behind it and the explanation here in the community from HIC is great) because applying a while-loop to create such matching-table (for associating, joining, mapping) is IMO simpler and more flexible and performant. Means something like this (just simplified for dates but the main-logic for hourly timestamps will be the same):
t: load ID, date(FROM + iterno() - 1) as Date from Source while FROM + iterno() - 1 <= TO;
