index vs lookup

[johnsasikumar]

Hello All,

Am trying to optimize the performance of a dashboard that was built some time back. The existing dashboard has been created by joining atleast 10 lookup files in the same query for a panel. And over time the lookup has increased in size going more than 1,00,000 rows. This has caused a lot of problems in the join conditions made in the query.

1. I would like to understand what is the search performance differnce when data is from a lookup or loaded from index.

2. Does lookup command have limitations like the join command like, what is the max limit for a lookup command. Can it be used instead of join when data is from a lookup.

3. Is there a difference in join limit set in limits.conf for data from Index and data from lookup. I have a scenario where the limits.conf default value for join and subquery has been increased and also am using max=0 in my join. But the results are not coming as expected. It works perfectly when i optimize the subquery having lookup to have less than 50,000 rows.

Any thoughts or advise on this.

[codebuilder]

This is a great opportunity to implement a accelerated datamodel or use KV Store, and move away from lookups.

Lookups are not indexed and become slower as their size increases. Think of the performance difference between a plain SQL query and a stored procedure, same logic applies.