Optimized load is much faster and therefore preferable, especially for larger data sets.
Optimized loads are possible if no transformations (calculations) are made during the load, and no (almost) filtering is done in a WHERE clause.
The only acceptable filter is using function Exists:
So for example this load can't be optimized:
Load * From ... WHERE Country = 'US'
but this load can:
Load * From ... WHERE EXISTS(Country)
On the other hand, the only time you want the load not to be optimized, is if you load a Mapping table from a QVD file. In this case, Optimized load doesn't quite work, so you want to make the load non-optimized deliberately.
On one hand we say that we should have try and have all the calculations in the script itself rather than putting them in the Dashboard & on the the other hand if we do that the load would no longer be Optimized. What to do then, how to have the expressions in the script itself and still make the load Optimized
The article by Rob states like this.
"Consider a QVD file with one million rows. The QVD was loaded from a database and contains two fields:
aNum – unique integers, 1M unique values.
aDate – dates distributed equally throughout 2000-2003, 1,460 unique values.
QV stores each of these values as integers, occupying 4 bytes of RAM each. Nice and compact."
Does this mean that the values in a QVD are stored as integers? Can anyone explain how the data is stored internally in a QVD?
QVD is storing unique data with pointer to the actual values. Every field has its own table and pointers to that value. Pointers are bit stuffed and they are used to lookup actual values. Result of this kind of keeping is better compression. This compression is done every time when load script is running.
Does anyone know why when loading data in loops with concatenation without where or transformation is not optimized?