How qlikview can handle a billions of records?

[#1]

hi all,

As the title, i want to know the performance of qlikview, hw sizing, scripting dan development(qvd generator) strategy. That's is  a big questions for me.

I already tried to load 200.000.000 with 12 column(field) and the speed is not so fast from that table i got qvw file with size around 7Gb. I'm running on windows server 64bit, 32core processor, and 32GB of ram.

From there, as i see the performance is not really good and the loading of data is took a long time.

I need to know is there anything that i can improve again and what is the best practice to handle a huge data.

Currently, i'm create a qvd generator then my qvw app get the data from qvd. from my qvw app, is there any other way to "reload"? i want to reload only the new record, because if reload all data again even from qvd, it will took quite a long time.

thank you

[#2]

hi there,

just about two days ago i do a sib. In this sib i'm handling more than 2 billion of record. The data was taken from oracle and i'm running on windows enterprise 64bit, 2cpu(24core) and 64GB RAM.

The loading time:

1. from oracle to qvd is around 5 hours, 1 table with 8 fields. RAM consumption during this process is around 30 - 40GB.

2. from oracle to my qvw(with others small table as dimension), is less than 1 hour, RAM cons is 30 - 40GB with average cpu usage is 90%.

That's my experiences

[stevedark]

The biggest single factor with load speeds from QVD is whether you are doing an optimised or non-optimised QVD load.  If you look at the progress window when the load is taking place it will tell you if the load is optimised, but says nothing if it is not optimised.  I have read that an optimised load is up to 100 times quicker, so it makes quite a difference.

Basically to get an optimised load you must ensure that you don't do any transformation on any data when loading from QVD.  This includes adding derived fields, modifying existing fields or loading a single source field in to more than one output field.  Renaming of fields is fine.  You also can not do a basic where statement on the load from QVD - it must be a single where exists.

Other things to consider when you get up into billions of rows is removing any redundant columns, avoid joins in your data model and reduce granularity of your data items (eg. round numbers down if possible).

Obviously extra CPU will help with processing of data - but the main thing hardware wise is to ensure that you have sufficient memory in the machine.  The more you can get in the box the better.

- Steve

[#4]

Hi Aditya,

Have your problem got solved? What approach you have followed?

Please explain so that it would help me and others.

Thanks,

Anosh

[stevedark]

You may want to take a look at my blog post on getting your data model right:  http://bit.ly/tBjL97

- Steve