Search performance very slow when Yes/No column is searchable

[Fabian_Weller]

I have several Yes/No columns in a table. If I enable "Searchable", the search will get very slow.

If I change them to type Enum, it will speed up the search.

This makes no sense to me. When I enter a search term like "Hello", why should a Yes/No column slow down the search speed?

P.S.: I need the Yes/No columns to be searchable, because I want to search them in the Filter.

[Steve]

Escalated.

[Fabian_Weller]

Hi @Steve do you know when this will be fixed?

[Steve]

I do not. I escalated it but received no response. I'll try again.

[Steve]

Please engage Support for help with this. Ask Support to assign the ticket to Gil Littman. In the ticket, please identify an app that reproduces the problem, and detail the steps to reproduce it.

[Gil · Accepted solution]

Hi Fabian,

Let me try to explain what's going on:

Your app has multiple searchable Y/N columns and they all have a LOOKUP expression as their display values. For Y/N columns, the display value is not calculated on sync, but when the column is displayed (or searched, in your case). When you search this table, the app needs to run the LOOKUP expressions for all the searchable columns for all the rows, which explains the long delay you're seeing.

You could try to use virtual columns to do the LOOKUP, because virtual columns are calculated by the server at sync time. If you make the VC searchable instead of the other column the search should speed up, but it might slow down the app's sync, so I'd recommend you keep an eye on the sync times when making this change.

[Fabian_Weller]

Hi @Gil thank you very much this makes sense. We tested it: When we use text instead of a LOOKUP expression as their display values, the search is way faster.

Another question: As mentioned, when I change them to ENUM columns with Base Type Ref, the search speed is much faster. Is this because the ENUM's display value is already calculated on sync?

[Gil]

Right. That's because enum don't let you set a display name for the column's value, only for the column's name.