Indexes on Persisted Derived Tables in SQL Server

  • 9 January 2018
  • 1 reply

Userlevel 1

Is there a way to make Looker create clustered indexes, or indexes with included columns or even just composite indexes? Currently the indexes created by Looker are single column nonclustered indexes, and therfore largely useless for filtering due to the overhead of performing RowID lookups against the base heap table.

Due to the way that SQL Server works, the number of rows returned by the filter on the index would need to be about two thirds the total number of pages in the heap; this is typically a fraction of the table. In my example, I have an approximately 850k row derived table, and the specified indexes will only be used if they will filter to a level of around 3,500 rows.

Clustered indexes, or covering indexes, can allow a significant leverage of performance.

1 reply

Userlevel 3

Hey there Matthew, as you pointed out above:

Currently the indexes created by Looker are single column nonclustered indexes

One partial solution we’ve seen used before is a concatenated field:

dimension: concatenated_index {
type: string
sql: CONCAT(field1, field2) ;;

Which can then be used as an index, however this would require filtering on this concatenated dimension, which would require using “contains” rather than “=” which increases the complexity of the comparison.

I’ll bring this up with the product team, to let them know this is affecting your PDT performance.