1

I'm studying for Marketing Cloud consultant exam. One of the questions relates to performance of the two functions Lookup Function and LookupOrderedRows function, specifically which is faster?

I couldn't deduce exactly from the official documentation as the wording is a bit confusing.

Thanks.

2 Answers 2

6

Lookup function is used to pull a specific field from a specific row in a specific DE. Basically it is the combination of LookupRows() > Row() > Field() functions.

LookupOrderedRows function is used to grab a specific amount of rows from a data extension (or similar object - Data View, Mobile List, etc.). In order to grab a value from this rowset you would need to utilize the Row() and Field() functions. If you have more than a single row, then you will also need to utilize a FOR loop to iterate through each row to collect the required values.

LookupRows (added in for completeness) - Is identical to LookupOrderedRows except for two things. 1 - It does not allow you to control the output. 2 - It does not allow you to do a sort on the returned results.

As for what is the most efficient or fastest, well that depends on use case:

  • If you just need a single value from a row in a DE, then Lookup is your best solution.
  • If you need a few values from a row (or multiple rows) that can be filtered into a group via conditional statements, but does not need to be sorted or controlled, then LookupRows is your function.
  • If you need a specific number of rows returned, and they have to be in a specific order then you will want to utilize LookupOrderedRows.

For example:

  1. Bob needs to grab the price on a product, which is stored in a different DE. You can use Lookup to get this value without needing to return a rowset first.
  2. Bob needs to grab price, product name and sku from a few products he has in the DE and does not care about order or amount returned. They are all related via order number. He can then use LookupRows to returned based on order number filter and then utilize Row() and Field() in a for loop to return the values most efficiently.
  3. Bob needs to grab only the first three product info (price, name, sku, etc.) from the order and needs them sorted from most expensive to least. He would then utilize LookupOrderedRows to grab this rowset and then a for loop with Row() and Field() to get the specific values.

All of this being said, the processing difference between LookupRows and LookupOrderedRows is not all that great and only comes into play when you are either pulling a high volume of rows or are utilizing in a process heavy environment. And Lookup should only be used for 'single value' calls as 2+ Lookups (that could have been a LookupRows) can be a significant resource draw increase compared to LookupRows.

5

Lookup returns a single column value from a single row.

NOTE: If the name/value pairs you're using in the Lookup function match more than one row in the Data Extension, a single column's value is still returned. The row chosen for the return value cannot be specified and should be not considered consistent.

The LookupOrderedRows and LookupRows functions return a rowset that you must retrieve column values from using the row and field functions.

Lookup is faster if you just need a single column value from a single row. The LookupRow functions are better when you need to loop through multiple rows or if you need more than one column value from a row.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .