topic Speed of loading a QVD file in QlikView

Speed of loading a QVD file

Tue, 05 Jan 2010 15:01:36 GMT

Hi All. I've got a qvw which I load by loading the existing data from a qvd file, loading new data, then saving the qvd. The idea of this is that the data load should be much quicker, and only pull new data from the server which should be slower, but will involve much less data.

I've been having time out problems when it is scheduled, so reloaded it in qv and monitored the progress. As expected the loading of the records from the qvd file was fast (35 million rows, about 70 columns wide), but it then sat there using 25% of the CPU (4 dual core processors) for 50 minutes before moving to the next step which was to check for the most recent data in the data loaded.

Any ideas why it is taking so long after the data is loaded (does it need to be ordered when saved or something like that), and is there any reason that the CPU is capped at 25%, querying the qvw via internet explorer uses the full 100%.

Thanks,

Richard

Speed of loading a QVD file

Tue, 05 Jan 2010 15:33:48 GMT

Hi Richard, when you load the QVD file, the message is (qvd Optimized ) 30,000,000 loaded ?May be in the script you have script like If, or group by, in my case work's on 300,000,000 the time on load is 1 hour the CPU Xeon 3Ghz.

Regards.

Speed of loading a QVD file

Tue, 05 Jan 2010 16:53:04 GMT

Hi Richard,

I had a similar problem where the load from the QVD included several manipulations of the incoming data. The following snippet illustrates my solution, loading to a temporary table and then reloading with my data manipulation - in your case you'd get the incremental data in the second load:


TmpIncidents:
// Load base incident data
LOAD
 *
FROM
 Incidents.qvd (qvd);

Incidents:
// Reload incident data for manipulation
LOAD
 IncidentID,
 If(IsNull(CausedByID), -99, If(CausedByID = 0, -99, CausedByID)) AS CausedByID,
 %ClipTypeID,
 IncidentLogged,
 IncidentClosed,
 If(IsNull(POSuiteHierarchyCatID), -99, If(POSuiteHierarchyCatID = 0, -99, If(Trim(POSuiteHierarchyCatID) = '', -99, POSuiteHierarchyCatID))) AS POSuiteHierarchyCatID,
 If(IsNull(POSuitesOtherCatID), -99, If(POSuitesOtherCatID = 0, -99, If(Trim(POSuitesOtherCatID) = '', -99, POSuitesOtherCatID))) AS POSuitesOtherCatID,
 IncidentImpact,
 IncidentStart,
 IncidentEnd,
 IncidentType,
 ...
 Trim(LoggedByFirstname & ' ' & LoggedByLastName) AS LoggedByName,
 ...
RESIDENT TmpIncidents
WHERE
 NOT IsNull(IncidentLogged);

DROP TABLE TmpIncidents;

Speed of loading a QVD file

Tue, 05 Jan 2010 16:59:46 GMT

Thanks for the reply. Normally it is loaded optimised, i.e Load * from C:\File.qvd; If I add a where clause to this load, the spead of the reading of the qvd file slows (takes about 5 or 6 times longer), but the CPU time after is still the same.

Speed of loading a QVD file

Tue, 05 Jan 2010 17:02:19 GMT

Thanks for the reply, but all the fields are loaded without any filtering, so it already uses Load * from qvd

Speed of loading a QVD file

Tue, 05 Jan 2010 17:08:22 GMT

In that case try putting the WHERE clause on the second load and not on the load from QVD.

The important point is that to get an optimised load from a QVD you mustn't do anything to the data during that load (including looking at it, which is what you're effectively doing by restricting it with the WHERE clause).

Speed of loading a QVD file

Tue, 05 Jan 2010 17:09:13 GMT

P.S. Sorry about suggesting your post as an answer - finger trouble!

Speed of loading a QVD file

johnmart61 — Tue, 20 Apr 2010 19:40:52 GMT

I just wondered if you ever found a solution to this problem?? I have the exact same thing happening - version 7.51 on a 32-bit server. Straight load of a qvd - LOAD * FROM xxx.qvd - no WHERE clause or data manipulation. It gets to record 1,173,648 int the qvd within a couple of seconds, then stops. It is not hung - every 5 to 10 minutes it will progress another few thousand records. It's using 25% of the CPU, as you said.

Even odder- if I specify the fields to load and exclude 5 specific fields (out of 19 fields), the data loads in seconds. All 5 fields are fully populated with data - basic Text information.

Speed of loading a QVD file

Tue, 20 Apr 2010 19:55:41 GMT

The CPU is at 25% because QV uses only 1 core at 100%. What about memory? Maybe, QV is using all the physical memory.

Speed of loading a QVD file

prieper — Tue, 20 Apr 2010 22:21:08 GMT

When there is no calculation of data or linking with previously loaded data, can only guess that it is related to available memory. CPU-load is not the key in this case. Have you checked this?

Peter

Speed of loading a QVD file

johnmart61 — Tue, 20 Apr 2010 22:26:40 GMT

You're the second person to suggest this. I''l try the same load on a 64-bit server - maybe that will resolve the problem.