These best practices reflect recommendations shared by a cross-functional team of seasoned Lookers. These insights come from years of experience working with Looker customers from implementation to long-term success. The practices are written to work for most users and situations, but as always use best judgement when implementing.
Ensure that queries are built and executed optimally against your database. Utilize cache whenever possible to reduce database query traffic.
- Build Explores using many_to_one joins whenever possible. Joining views from the most granular level to the highest level of detail (many_to_one) typically provides the best query performance.
- Avoid joining views into Explores on concatenated primary keys declared in Looker. Join on the base fields from the view or persist the view with the compound primary key using a persistent derived table for faster queries.
- Maximize cache usage by applying datagroups within Explores using the persist_with parameter. This enables Looker to integrate more closely with the backend data pipeline so that cache usage can be maximized without the risk of analyzing stale data. Named caching policies can be applied to an entire model and/or to individual Explores. By default, Looker caches queries for 1 hour.
- Warm the cache for popular dashboards using schedules with datagroups. When the dashboard runs via the schedule, all Looks and Tiles will be run, causing the underlying queries to be loaded into the cache.
- Use the dashboard auto refresh feature strategically. If a dashboard uses auto refresh, make sure it refreshes no faster than the ETL processes running behind the scenes.
Looker Server Performance
Ensure that the Looker server and application are performing optimally.
- Limit the number of elements within an individual dashboard. There is no hard and fast rule to the number, since the design of the element impacts memory consumption based on a variety of factors, but dashboards with 25 or more tiles tend to be problematic when it comes to dashboard performance.
- Avoid over-using pivots within Tiles and Looks. Queries with pivoted dimensions will consume more memory. The more dimensions pivoted, the more memory that's consumed when the dashboard is loaded. Ensure that pivots are used strategically.
- Limit the number of views included within a model when a large number of view files are present. Including all views in a single model can slow performance. When a large number of views are present within a project, consider only including the view files that are needed within each model. Consider using strategic naming conventions for view file names to enable easy inclusion of groups of views within a model. An example is outlined in our documentation.
- Avoid returning a large number of data points by default within dashboard Looks and Tiles. Queries that return thousands of data points will consume more memory. Ensure that appropriate filters and required filters are applied to limit the data set as much as possible upon initial dashboard loading.
For more help identifying the source of performance issues, check out this User Forum post.