Combining ID and Description Columns(State ID , State Name) for better performance during filtering or in where clause

Filter dropdowns on attributes have to show the description in suggestions, but in the SQL should resolve the Key or ID of the dimension in the where clause. This is important to avoid performance issues. The Key columns are index, whereas the description columns are not. Our warehouse is pretty large and houses close to 2 TB data and growing.

Taking a simple example of state table, that has three columns, State_Key, State_Abbr and State_Description. State Description is pulled onto the look as a filter and Tennessee is selected. The SQL generated should be as below for optimal performance.

Select State_Description from State

Where State_Key = 45

Thanks in advance!!

6 replies

Userlevel 6

a big +1 here. When we have derived tables and we use templated filters to better performance our derived table sql explodes since we need to join in all the mapping tables to get from the ID to the string values for the many templated filter values we want to use.

Userlevel 2

Hi @SadhanaAmpalam and @IanT,

I am happy to pass this along to our product team. This seems like a reasonable feature.



Came here to request this exact feature. +1 From here as well. This would give us some massive performance gains.

Userlevel 3

big +1 from my side

Userlevel 3

would be great, if looker can implement this enhancement

Any news on this feature ?