Indexing columns in the database to speed up domain-based reports

Sometimes a domain-based report runs really slowly, for no apparent reason. One possible improvement would be to index some fields in the underlying database query. The way to proceed would be:

  • Set the logging level to DEBUG for SQL queries - that will allow you to see the underlying query that the domain-based report generates that is then passed on to the database.
  • Then run that query directly on the database to check for performance. You can then tune the database by indexing the field in the query that would have the best impact on report execution

Regarding materialized views: they are just SQL statements--you have to put indexes on the tables that pertain to the materialized view queries to get better performance from them.

You should actually get better performance on the switch from derived to actual tables, if you are using derived tables, because derived tables are sub-queries/sub-selects and are intensive performance-wise--much more so than a direct table access with some security level filters applied.

It's all about how the SQL ends up being generated by the domain as well as how the optimizer optimizes that SQL--coupled with the table indexes involved in the joins.

Other Performance Tips: