2 posts tagged with "Performance"

BigQuery BI Engine accelerates SQL queries using an in-memory execution service. When a query scans a pinned table, response times drop from seconds to milliseconds.

Reserving memory in BI Engine works like renting an assigned parking space in a garage. Management assigns you spot #42. However, every night the maintenance crew repaves the garage and paints new numbers on every stall. When you pull up in the morning, spot #42 is gone. Your permit still points to a space that no longer exists, and security redirects you to the slow overflow lot three blocks away. That is what happens when Looker rebuilds Persistent Derived Tables (PDTs) with new physical table names during each refresh.

In embedded dashboards, customer-facing applications demand sub-second query speeds. Pinning tables in BI Engine keeps critical data in memory, but declaring a table as preferred is only half the battle. To guarantee instant responsiveness, you must also eliminate the first query cold miss with cache pre-warming when your tables rebuild.

Configure table pinning when:

• Embedded analytics and customer-facing portals require consistent sub-second response times.
• Ad-hoc analyst queries repeatedly evict dashboard data from your BI Engine memory reservation.
• Your queries target Looker PDTs or pre-aggregated rollups that rebuild on scheduled intervals.

Have you ever opened a Looker dashboard with ten filters, watched the tiles spin for longer your expected, and wondered why it’s taking so long when the underlying database is usually fast?

The culprit is often a silent connection storm with the tiles AND the dashboard filters. By default, Looker issues separate, concurrent database queries for every type: string filter on a dashboard the moment it loads, just to populate the suggestion dropdowns. It is the database equivalent of a restaurant kitchen prepping every single menu item the second a customer sits down, before they have even opened the menu.

Before trying to optimize these queries, ask yourself: do your users actually need dynamic suggestions for all of these filters? If a field has high cardinality (like a user_uuid) or if the values are static and change only once a year, you should not query the database for suggestions.

Let's look at why this happens and how to manage suggestions in LookML to protect your database connection pool.