I have a bit of a conceptual question that I was hoping someone might have some insight on. Suppose I have a table such as the below:
Data:
Load [KeyField1],
[KeyField2],
[KeyField3]
Resident Whatever;
And to this table I want to join a very wide table, let's say sixty columns wide, but with only 50 rows. Would it be more efficient in general to have a single join such as:
Left Join(Data)
Load *
Resident WideTable;
Or, would it be better to do multiple joins, like so:
Left Join(Data)
Load
[KeyField1],
[KeyField2],
[KeyField3],
NormalColumn1,
NormalColumn2,
...
NormalColumn20
Resident WideTable;
Left Join(Data)
Load
[KeyField1],
[KeyField2],
[KeyField3],
NormalColumn20
NormalColumn21,
...
NormalColumn40
Resident WideTable;
etc.
In other words, how does joining a very wide table impact performance as opposed to joining multiple tables that are not as wide (I'm working in Qlikview 11.20 SR 12).
Another option to consider is using mapping tables and applymap to optimise your data model. Applymap will be much faster than joins and avoids the problem of multiple matches creating additional rows in your data.
This may not be ideal with the number of flag columns you have but may make the logic simpler than loads of "if"statements.
If things are running okay then it sounds like leaving it be may be the best approach. However, if you are hitting speed or memory constraints then taking the hit on doing a CROSSTABLE and then rebuilding the front end would almost certainly pay dividends.
Over that number of rows being able to do an incremental load would also be a massive help.
Where I save a massive amount of time on a reload (from many hours down to a number of minutes) was chunking up the data based on region. The load I picked up was joining a lot of data from two different QVDs. Both QVDs had data from a number of different regions - and the region was stored in both. Rather than joining the whole lot in one I looped for each region and joined just one region at a time. After each join I stored the joined table to a new QVD (again by region). The front end then looped to pick up data from each of the combined QVDs.
If you can do something similar that would be a good thing.
