topic Volume initial load performance in QlikView

Volume initial load performance

Tue, 05 Jul 2011 16:56:57 GMT

I have a Windows Server 2003 Enterprise x64 Edition Service Pack 2 with thefollowing settings:
1) Intel XEON 1.60GHz
2) 16 GB RAM

I’ve install Qlikview 64 bit SR3 10.00.9055.7 Edition (x64) and I'm loadingaCSV file (10 Gb) with 100 million records distributed in 8 columns, whoserecord maximum length can be 50 characters and it stops at 77.5 million recordsafter 15hrs of making the initial load.

The competition’s tool does load in 4 hrs, the intention is to improve this timeto give a solution as a tool for BI.

Does anyone know how to optimize the load volume?

Greetings!

Volume initial load performance

Miguel_Angel_Baeyens — Tue, 05 Jul 2011 17:00:07 GMT

Hello Jose,

Are you loading the file directly or via driver? Is it only one file or multiple files in a loop?

Anyway, note that you can LOAD the CSV file into QlikView, then STORE into a QVD file and then use this QVD file for anything, and, just in case, load the non-existent records from the CSV storing always in the QVD. Have you tried loading from QVD?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Re: Volume initial load performance

Tue, 05 Jul 2011 19:04:40 GMT

Hi Miguel Angel

The load is direct

There is only one file

I've already store the load in QVD file

What I've done is split the file in 2 QVDs files, it works perfect, cause it finished in 15 min each file. Now I'm trying to concatenate both files in one QVD file, I hope it works.

Do you have another suggestion?

Thanks in advance.

Volume initial load performance

johnw — Tue, 05 Jul 2011 23:54:33 GMT

If loading half the volume can be done in 15 minutes, but the full volume takes 15 hours then stops, something is definitely wrong. Typically I'd say "you don't have enough memory", though it looks like you do on the surface. Have you watched your memory consumption during the 15 hour load to see if it maxes out?

Re: Volume initial load performance

Mon, 11 Jul 2011 13:49:02 GMT

Thank you Guys, I already finished with it, I load the csv file in QVW file. It works perfect, I also generate a QVD file. The compression from the 10 Gb file at the end is 6 Gb. For certain reason, I couldn't load the QVD file. So I decided to load the QVW file as Binary, it takes me just 2 minutes.

Thank you for your suggestions.

Volume initial load performance

Fri, 23 Sep 2011 15:33:21 GMT

I'm having trouble w/ the syntax to create a qvd from a csv. (I am fine with select * from a database). Can you give me an example?

Volume initial load performance

Miguel_Angel_Baeyens — Fri, 23 Sep 2011 17:45:10 GMT

Hi Anna,

Go to the script editor (Ctrl + E) and use the "Table File" button in the bottom part of the dialog and follow the wizzard that will show up. That will help you loading the CSV file, setting the separator (or fixed positions, if any).

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

Volume initial load performance

Thu, 06 Oct 2011 13:32:11 GMT

all set! got busy, hence my delayed response but did want to say thanks!