LOOKUP vs SELECT and efficiency

Hi all - just 2 style/clarification questions about overlapping functionality:

LOOKUP() vs. SELECT() ?

INDEX(SELECT( … ), 1) vs ANY(SELECT( … )) when you’re sure there’s only one result, and it needs to be typecast as its type, not as a returned LIST.

Interested in some guidance both in efficiency and best use cases. Any advice appreciated.

Thx!

1 15 2,657
15 REPLIES 15

Steve
Participant V

I’d assume ANY(…) and INDEX(…,1) are equivalent in performance, if that is what you’re asking.

Actually, it my impression is that INDEX() is far superior to LOOKUP() when you have a small table or slice (just one or a few records). I recently learned from @MultiTech_Visions about creating a slice of one item (a user table) and then using INDEX(…,1) instead of LOOKUP(). LOOKUP() was causing warnings for my format rules but index does not cause this error. So, I assume that INDEX() is more efficient, at least in regard to format rules.

I thought I might write a really short tip saying “in situations where your table or slice is small, use INDEX() instead of LOOKUP()” but I thought I should first check to see what others have written about this and that brought me to this thread.

P.S. The thread in which I got a very useful tip from MultiTech Visions is here:

Hi Kirk. I’m not sure what point you are trying to make here.

The question here, and my answer, were about comparing INDEX vs ANY. Not about comparing INDEX vs LOOKUP.

INDEX is absolutely a more performant expression than LOOKUP. But they also do two entirely different things though. If you need to lookup a value, you can’t just use INDEX. So I don’t see the point in making any comparison there.

Matt’s advice about using INDEX, or alternatively ANY, on a single-record Slice is definitely a great strategy. You can certainly compare using LOOKUP to the entire strategy, just not to INDEX itself.

Causing errors vs not causing errors is not an indication of efficiency, or the lack of. How was LOOKUP causing errors? Perhaps you weren’t using it quite right?

First of all, I need to apologize for misunderstanding your comment and not explaining myself properly. Sorry.

My point was about what @MultiTech_Visions wrote about INDEX() being a better option in some situations. Here’s the situation where I found, thanks to MultiTech_Visions, that INDEX() did the same work as LOOKUP() but without a warning:

I had a table with a single row that I was using for settings and format rules. I used LOOKUP() from another table to determine which records should be shown or hidden and I also used LOOKUP() to access the same settings information for format rules. I didn’t get an error but I did get a warning that my use of LOOKUP() could cause performance issues:

3X_f_7_f70a8d722b78b862fcc5f7e11cd450c0d6d1a9dd.png

Then, indeed, I found on my Android device that the performance seemed to be getting worse, though I didn’t notice a problem on my computer. I found a workaround that combined virtual columns and an action that seemed to work so I posted the “tip” I linked to above. Then, I learned from MultiTech_Visions that using INDEX() is a much better solution in my situation.

In a sense, MultiTech_Visions has already given us this lesson in the following tip:

I thought, though, that it might be worthwhile to isolate the point about using INDEX() instead of LOOKUP() when possible, which is one aspect of that tip. I looked for discussion of that specific point and that brought me here. But, I guess there wasn’t a good match between the thrust of this discussion and my point of interest. Once again, sorry for the confusion.

P.S. I think that in most situations the use of INDEX() we are discussing here doesn’t make sense without first making a slice to reduce the data to a single row. In my case, however, I had a table with a single row to begin with so, for me, the lesson was less about using a slice and more about substituting INDEX() for LOOKUP() when a table only has one row (or, perhaps, a very small number of rows).

Agreed

  • They both take the first item out the list, and I imagine they have the same processing time.

There is a difference in certain circumstances though, which make INDEX() a better option to make your “go to” - this way when those certain situations come about you’re covered.

Steve
Participant V

I prefer IN(..., Current_User[...]) to (... = INDEX(Current_User[...], 1)).

Thanks for the reply. I think I must be slow on the uptake, so to speak, but I’m not sure I understand. Are the IN() and INDEX() expressions you are writing about TRUE/FALSE? If so, that’s different from what I am looking into right now. The INDEX() usage I was referring to is equivalent to LOOKUP() in that one doesn’t need to know what one is looking for, just the location of the cell one wants to check.

Steve was simply giving a third option along with ANY() or INDEX(…,1). I don’t believe it was in response to anything you said.

IN() can be useful if you may be dealing with a Slice of more than one record, for example if some user can have more than 1 role.

If you are in fact using a Slice with always only 1 record, I personally would not use IN(), in order to retain better readability of your expression, as using IN() might imply the existence (or the possibility of existence) of more than 1 record in the Slice.

As always, I appreciate your response. I think I understand your point about IN().

I don’t want to be overly persistent in asking about INDEX() in this thread. I was just intrigued by @MultiTech_Visions use of it and was under the impression that a lot of AppSheet creators might not be aware that, in single-record tables/slices, INDEX() can be preferable to LOOKUP().

You know… I’ve never thought about creating multiple “role” records for a user…

using IN() might imply the existence (or the possibility of existence) of more than 1 record in the Slice.

How interesting. That's my reaction when I see INDEX--i.e., specifying the first value from a list implies to me that the list likely comprises multiple values. On the other hand, that's not how IN strikes me--i.e., checking the presence of a value in a list doesn't imply to me that the list likely comprises multiple values.

dbaum
Participant V

This is a helpful thread. Thanks to all contributors.

I think it would be handy to synthesize all the guidance in these posts into consensus best practice(s) for referencing a column value from a single-row table/slice. I infer one best practice pretty clearly, and have a question about another.

1. Best practice when you need to retrieve the value (e.g., to populate part of an expression for a property like App Formula, Initial Value, or Suggested Values) or compare it to multiple values

Use INDEX

Examples:

CONCATENATE("Comment by ", INDEX(CurrentUser[Name], 1))
IN(INDEX(CurrentUser[Role], 1), LIST("Admin", "Leader))

2. Best practice when you need to compare the value to a single other value

Between IN and INDEX, is there a performance benefit (e.g., processing efficiency, universal applicability) of one over the other? Or is it simply a personal preference, perhaps based on perceived readability for developers or desired consistency with best practice #1?

Examples:

"Member" = INDEX(CurrentUser[Role], 1)
IN("Member", CurrentUser[Role])

 

The best practice is not the usage of INDEX to lookup a value. The best practice is to create a single-record slice when you can, so that any expression used to lookup the value from the slice is very efficient.

It's personal preference.

Top Labels in this Space