In lookups in Informatica Devloper mappings on our enviroment, I reguarly see '--' in the Lookup sql overrides. It is at the end of the script. Normally I 'd interpret a '--' as 'comment out' of sql.
Though a colleagues says it has to do with the fact that some kind of ordering will be applied. But I can't find more info about it on the net.
Can anyone tell me what '--' at the end of a Lookup sql override mean or do in a lookup? Or where to find more info about it?
Your answer is correct: Informatica by default adds the ORDER BY statement to the SQL Override. The '--' at the end of the statement disables it. BUT:
Informatica does that on purpose. It's there to make the cache building faster - cache is sorted to improve the cache reads. It should be reflecting the order of ports mentioned on Condition tab of Lookup Transformation
If the ORDER BY is disabled, you will probably see the following message in session log indicating a possible performance degradation:
Message: Warning: Unsorted input found when building the cache for the Lookup transformation [lkp_name]. ...
In general this should be avoided. (Well - the SQL overrides should be avoided at all. For many reasons. But that's a different story.) However in some special cases this might be needed and be fully justified.
Example:
The Condition is defined to do the matching on ports A, B, C - but the rows are not unique and we need to get the latest row using updatedOn. In this case we might use a
Use First Value for Lookup policy on multiple match property. And to ensure that the most recently updated row will be first we include our own ORDER BY A, B, C, updatedOn desc -- clause. This will make sure that data is sorted by the condition columns as we need, and the most recent row will come first. And -- needs to be there to prevent the second ORDER BY clause that Informatica will add anyway.