Non-optimised load faster than optimised load

jonathandienst — Thu, 24 Nov 2016 11:33:37 GMT

A colleague recently uncovered a rather unexpected result. Using 11.20 SR15, this script ran an optimised load

// Optimised load - 39 sec for 5.8m rows

LOAD

FROM [somerealdata.qvd] (qvd);

While this script loading the same data, but non-optimised, runs significantly (almost 2x) faster.

//Non-optimized load - 21 sec for 5.8m rows

LOAD

FROM [somerealdata.qvd] (qvd)

WHERE 1=1;

Watching the task manager, it could be seen that the optimised load was single-threaded (why would this be, Qlik?), while the de-optimised load was multi-threaded. This was running on a 32 processor machine (dual 8 core Xeon hyperthreaded) and most of the processors got roped in on the latter load. So you might not observe the same on a less powerful machine or one that is already heavily loaded.

We then tried a small variation. and got the same result:

//Also non-optimized load - again 21 sec for 5.8m rows

LOAD

1 as Count

FROM [somerealdata.qvd] (qvd);

We replicated the loads several times and the results were to all intents and purposes identical. This test was using real world data. and as we load up to 100m rows in complex multi-step ETL processes, this could have a real impact on how quickly processes run

I would welcome input from one the Qlik technical experts (for example, about the threading) and any observations, especially if someone else has observed similar behaviour, including on recent version (V12 or 12.10).

topic Non-optimised load faster than optimised load in QlikView

Non-optimised load faster than optimised load