topic Re: How to optimize the loading performance. in QlikView

How to optimize the loading performance.

Wed, 05 Sep 2012 02:20:57 GMT

Hi All,

I use join function to get the dataset from 2 QVDs, but I found the loading speed is not very fast.

Could you help me out?

LOAD OpportunityID,
OpportunityNumber FROM Opportunity.qvd(qvd);

inner join LOAD ActualID,
OpportunityNumber FROM Actual.qvd(qvd)

It uses OpportunityNumber as the join key, and the value for that key looks like AP-12345.

Is there any way to make it optimal.

Thanks.

Re: How to optimize the loading performance.

jvitantonio — Wed, 05 Sep 2012 07:16:29 GMT

Hi, this load should be optimized. Are you using any where conditions or just loading the file? Make sure that when you load the qvd file it says "... (qvd optimized)". If it is, then you may need to think about changing your string key by an integer key. They are always faster and you can notice the difference with big data.

Re: How to optimize the loading performance.

Wed, 05 Sep 2012 07:54:45 GMT

Thanks for your reply, could I know how to convert string key to integer key? Is there any function to achieve it?

Thanks again.

Re: How to optimize the loading performance.

whiteline — Wed, 05 Sep 2012 07:59:12 GMT

AutoNumberHash()

But I think QlikView have already done it implicitly.

Re: How to optimize the loading performance.

jvitantonio — Wed, 05 Sep 2012 08:52:35 GMT

it can be

Load autonumberhash128(OpportunityNumber) as Key,

....

From...

Re: How to optimize the loading performance.

Miguel_Angel_Baeyens — Wed, 05 Sep 2012 09:00:50 GMT

Hi,

Even easier and likely faster, the AutoNumber() function will do, returning a numeric integer value for each possible value used as parameter. AutoNumber() allows the use of a second parameter, should you want to use the function with similar input values:

LOAD AutoNumber(CustomerID, 'Customers') AS CustomerIDKey, // will start returning on 1 for the first value of CustomerID

...

LOAD AutoNumber(VendorID, 'Vendors') AS VendorIDKey // will start returning on 1 as well for the first value of VendorID

Hope that helps.

Miguel

Re: How to optimize the loading performance.

Miguel_Angel_Baeyens — Wed, 05 Sep 2012 09:03:20 GMT

Hi,

A couple of things here. First is that you are performing a JOIN between two tables, so what it's likely to take longer is the JOINing of both tables, instead of the LOAD of them. Try not using JOIN to measure the pure load (extraction from QVD) time.

Second is that numeric values are handled faster when joining, so you better use the AutoNumber() kind of functions when storing into the QVD file, then the JOIN is faster.

Hope that makes some sense.

Miguel