Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
it can be
Load autonumberhash128(OpportunityNumber) as Key,
....
From...
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.
Thanks for your reply, could I know how to convert string key to integer key? Is there any function to achieve it?
Thanks again.
AutoNumberHash()
But I think QlikView have already done it implicitly.
it can be
Load autonumberhash128(OpportunityNumber) as Key,
....
From...
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
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