what is QV optimization?And how it is useful for qv applications

[#1]

what is QV optimization?And how it is useful for qv applications

[johnw]

What do you mean by optimization?

In a very general sense, optimizing a QlikView application means making it better and faster.  I hope it is obvious why making your application better and faster is useful.

You MIGHT be referring to an "optimized QVD load".  When you do an optimized QVD load, it loads from the QVD MUCH faster than a normal load.  To do this, you can have nothing in the where statement other than a single exists(), and you can do anything more than rename fields in the load.  Hopefully it is obvious why loading fast is useful.

[bimartingo]

Hi John,

what you suggest do do if we have a more complex where clause on loading data from a big qvd file?  Load the full data into memory and then generate a new table with the desired filtered data?

should we even avoid the simple functions like date(), monthname() on the load?

[johnw]

If things get complex enough, I'd probably settle for not having an optimized load, as it might be even slower to optimized load the whole thing and then do a ton of manipulation on it.  But my typical approach might go something like the below (which uses optimized loads to get active orders of several types for customers in California).  As far as functions like like date() and monthname(), those should generally already be in the QVD if desired - including them in the load will break the optimization, no matter how simple the function is.  Burn the time when building the QVD, not when loading from it, as you build it once, and may load many times.  This is particularly true when using incremental loads.  I personally use a calendar QVD, and typically store only the raw date in other QVDs.  I can join to the calendar QVD if I want fields like month, year, financial quarter, etc.  That helps enforce standards across your applications, so that you don't end up with 'Mar 2012', '2012-Mar', 'Mar-12' and so on depending on the application.

[Customers]:
LOAD 'CA' as State
AUTOGENERATE 1
;
LEFT JOIN ([Customers])
LOAD
LocationID
,State
RESIDENT Address.qvd (QVD)
WHERE exists(State)
;
INNER JOIN ([Customers])
LOAD
Customer
,LocationID
,Name
,CreditStatus
FROM Customer.qvd (QVD)
WHERE exists(LocationID)
;
DROP FIELDS
State
,LocationID
;

[Orders]:
LOAD 'Active' as Status
AUTOGENERATE 1
;
LEFT JOIN ([Orders])
LOAD * INLINE [
Type
Prime
Excess
Stock
Secondary
];
INNER JOIN ([Orders])
LOAD
Status
,Type
,Customer
,Order
,Amount
FROM Order.qvd (QVD)
WHERE exists(Customer)
;