topic Re: Loading Big Datasets in QlikView

Loading Big Datasets

Fri, 06 Feb 2015 05:32:38 GMT

Hi,

I am facing a problem wherein the query returns about 200 million records from a DB server and I am trying to load this into a qvd. It takes about 6 hrs to load 13 mil records.

The query is fast ans returns in under 20 mins. The bottle neck is in serving the data from DB into the QV.

Are there any means of speeding this up?

Thanks.

Re: Loading Big Datasets

tresB — Fri, 06 Feb 2015 05:35:13 GMT

Increase hardware(RAM, CPU) capacity.

Re: Loading Big Datasets

AbhijitBansode — Fri, 06 Feb 2015 05:40:17 GMT

Data load time is depend upon the hardware configuration of the machine on which QV is installed.

Also try to optimize your load process by implementing incremental load.

Do you really need 200 million rows of data in QV?

Re: Loading Big Datasets

jagan — Fri, 06 Feb 2015 05:56:23 GMT

Hi Gokul,

You can try incremental loading, instead of loading all the data daily you can load only current day data and store the previous data in QVDs.

Refer Qlikview help file for incremental loading or in community.

Loading data from QVD is 100 X faster when compared to database, also you will pull less data from database when you are implementing incremental loading.

Regards,

Jagan.

Re: Loading Big Datasets

Fri, 06 Feb 2015 05:56:54 GMT

Load data in chuncks ...might be it will help you...

Re: Loading Big Datasets

Fri, 06 Feb 2015 06:02:14 GMT

Thanks Tresesco and Abhijit. Unfortunately, we cannot increase the hardware.

Do you think incremental loading is going to help? Incremental loading is useful when we are not able to execute the entire query and we break the dataset and concatenate the result. Because the query has returned and its just loading records I am somewhat doubtful.

Re: Loading Big Datasets

avinashelite — Fri, 06 Feb 2015 06:37:01 GMT

Hi,

Try to follow the below points:

1.Eliminate unwanted columns this will reduce the data.

2.Try to aggregate the data as much as possible.

3.Try to push all the calculation to the script level.

4.If data is to huge then try to divided it as multiple qvd on yearly or region basic etc

Hope this helps you

Re: Loading Big Datasets

Fri, 06 Feb 2015 07:28:00 GMT

I want to add another point Split the data in multiple Script and run it parallel Process and generate multiple qvds. It will also reduce total data extraction process.

Thanks

Re: Loading Big Datasets

Fri, 06 Feb 2015 08:29:24 GMT

Hi Sovan, I think parallel processes will help here.

Could you please provide an example of how to parallelize load scripts?

Re: Loading Big Datasets

AbhijitBansode — Fri, 06 Feb 2015 09:07:30 GMT

Create different extraction applications to load the data from database.

Lets says you have 200 million records, create 5 separate app to load the data (40 million each) .

(You can increase no of applications depending upon performance). Have to specify the clause to distinguish this data.

Store them in separate qvd files and then you can load all the qvd files in single application and concatenate its data.