How to optimize the loading performance.

[#1]

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.

[jvitantonio]

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.

[#3]

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

Thanks again.

[whiteline]

AutoNumberHash()

But I think  QlikView have already done it implicitly.

[jvitantonio · Accepted solution]

it can be 

Load autonumberhash128(OpportunityNumber) as Key,

....

From...

[Miguel_Angel_Baeyens]

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

[Miguel_Angel_Baeyens]

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