QlikView straight table being slow when working with many columns (even though few rows)

[fredrikoskarsso]

Hello,

I have been looking into how the straight table performs with very many columns  (dimensions), and found that it is surprisingly slow even though there are very few rows. In this case, scrolling (both horizontally and vertically) has a noticeable delay and is not smooth at all. I have verified this both on my local machine ("normal" specs) and also on a development server (250GB+ RAM).

Does anyone have an idea why this is? In excel for example the same dataset works smooth as anything.

To test this, use the following code to generate 60 dimensions and 100 rows:

ManyDimensions:
Load 1 as ID AutoGenerate (1);

for i = 0 to 60

outer join (ManyDimensions)
Load
IterNo() as ID, 
Round(Rand(),0.00001) as Dimension$(i)
While 
IterNo() <= 7000;
Load * INLINE [
Dummy,
1
];

next i

Add all the dimensions to a straight table, with a bogus expression like "1". Try to scroll in the table and notice how slow it is.

After some more testing, I found that by adding the columns as expression, it is faster but still a bit laggy. To test this, add the following code to a new module (Tools -> Edit module):

Option Explicit

Sub GenerateStraightTable()

Dim myChart
Set myChart = _
ActiveDocument.ActiveSheet().CreateStraightTable()

myChart.AddDimension "ID"

Dim i
for i = 1 to 60
myChart.AddExpression "Dimension"&i
next 

end sub

Then add a button with a click action to run the macro called  GenerateStraightTable. The table generated will have one dimension and 60 expression. Faster, but still laggy. 

[marcus_sommer]

Qlik is designed as an analytics tool and has it strengths in the aggregation of data and to cover their associations over multiple tables, for example will a sum(field) or count(field) calculated against a few dimensions / dimension-values be very fast even by many millions of records.

Table-charts with so many columns are quite useless in regard to analyses the data and their creation with large virtual tables in the background and rendering their results within the UI are quite expensive - and it happens on the fly. Comparing it with the scrolling-behaviour of Excel is comparing apples to oranges.

If you really want to display many columns then would a tablebox be more suitable.

- Marcus

[fredrikoskarsso]

You are right, and I agree that QV is not the same as excel. Further, in most cases showing so many columns is not very good for analytics, but in this case I think it actually is necessary.

The things is, I want to be able to analyze the full production chain of a product, showing all possible different date stamps for operations. As there are around 40 date fields, I dont really know how to get around it, as I want to be able to show many different product chains in the same table. Further, some calculations should be made, so a tablebox won't work.

Anyhow, I think I will have to work around it, was just surprising that QV required so much calculations for straight tables.

[marcus_sommer]

This means your various date-fields are a quite big crosstable - even if the fields are distributed within several tables - the logically structure remains a crosstable. Therefore I could imagine that a transforming of them into a "normal" table-structure per The-Crosstable-Load would be more suitable because the chain of events could then be displayed vertically or maybe within a pivot with the dates as a horizontal dimension (I'm not sure if the last would be perform much better as the straight-table approach but it would be an option).

- Marcus