Dereference vs lookup()

[Foomeister]

What is the difference between dereference and lookup()? Both seems to find the references row to pull column value from another table.

Thank you!

[Suvrutt_Gurjar]

In general you are correct. As per my understanding ,

Both dereference and LOOKUP() can produce an identical end result.

However

1. Dereference expression needs the two tables to be related by referencing relationship. One can then get ( lookup) values from parent table in the child table through derference expression

2. Lookup does not need the two tables in reference relationship. However there needs to be at least one matching column / value between two tables to get a value from the other table.

3. As per my understanding, if the reference relationship is established between two table, it is better to use the dereference expressions in the child table if one wants to get multiple values from the parent table. LOOKUP() will perform a multrow operation each time.

Example : “Order Details” table ( Child table) references the “Orders” table through “Orders” table’s key column [Order ID].
One could get [Order Date] from the "Orders Table in the “Order Details” table by any of the below expressions
A) By dereferencing
[Order ID].[Order Date]

B) By LOOKUP()
LOOKUP([_THISROW].[Order ID], “Orders”, “Order ID”, “Order Date”)

However since the two tables are in referencing relationship , I believe dereference expressions are more efficient way.

The below help articles explain it very well.

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

help.appsheet.com

Dereference Expressions

Use a dereference expression to get a column value from the referenced row.

[Shawn_Crocker]

@Suvrutt_Gurjar Are we certain dereferencing is actually more efficient? I am in a position where I want to use a value from one of either two master tables depending on which master table a value from a third table is found in. From a mechanical stand point, I unable to see how dereferencing could arrive at the desired value without performing the same operations as the lookup() function. I’m just trying to justify reworking things a bit in my table structure to accommodate referencing two tables when lookup() would be a simpler approach unless it is indeed going to be a bit slower. Can anyone else also validate this performance inquiry? (no offence Suvrutt jus what to be certain). @Steve ?

[Suvrutt_Gurjar]

Hi @Shawn_Crocker ,

I am sure @Steve will add his comments that will be more insightful.

As per my understanding ( and as mentioned in the post above) if two tables are already in reference relationship through a “ref” type column in the child table, it makes sense to use dereferencing expressions, especially to pull multiple column values from the parent in the because the “ref” column has already established a “LOOKUP() like” relationship with the parent table. Using dereferencing expressions is better in this case instead of using several LOOKUP()s - one each for each pulled column.

If it is just one column and the two tables need not be in referencing relationship for other app requirements ( such as showing / performing math or similar operations on related child records) , then LOOKUP() is good to pull just one odd column from the other table.

As for exact performance aspects of either method, I will try to perform some tests on a large dataset and revert with any specific observations I have. Of course, I will do it as and when I get some testing time for that activity.

Hope this helps.

[Suvrutt_Gurjar]

In case the below helps- some performance testing.

Wrote some VCs - 4 VCs based on dref expressions and 4 VCs on based on LOOKUP() expressions. The child table where the information was pulled into from a parent table has 37, 784 rows ( App 37K) . So each VC timing is for expression computation of 37 K records aggregated.

The testing shows that the Dref expressions are marginally faster than the LOOKUP() expressions. The image below shows a pair of VCs , 4 such VC pairs ( Country, Continent, Capital, Population) constructed with one Dref and one LOOKUP() expression.

Of course, any such testing may need to be carried with more test scenarios. However, quick test results conducted over large dataset are below.

[Shawn_Crocker]

@Suvrutt_Gurjar Thanks so much for that! I’m setting myself up with some VC that each reference the two different tables. Now I can just use some logic to find which one evaluated to blank and use the other one. This does make the expressions nicer to read also.