Switching to "logical data source" available in Tableau from version 2020.2
The source developer can exert only a limited influence over the SQL queries generated by Tableau. These depend mainly on the operations in the workbook and are generated dynamically by the Tableau Engine. Prior to version 2020.2, the Tableau Engine always queried all the tables connected in the data source even if only a small percentage of the tables were actually needed in the workbook. Thanks to the logical level introduced in Tableau 2020.2, only those objects are queried that are actually needed. This makes the generated SQL queries much leaner.
Set performance options
Performance options is another feature in Tableau 2020.2 that can be set in the relationships between tables, allowing the developer to optimize the generated SQL queries.
The general rule here is: the more precise, the better. "Many-to-one" is preferable to "many-to-many". "All records match" is better than "some records match". The settings stop the Tableau Engine from carrying out unnecessary grouping and aggregation and this improves the performance of queries.
A little tip to close
If you use the same query in a source several times (to query all history objects, for example), it is worth writing this as a custom query and adding an "order by false" to it at the end. In many databases, such as Exasol, this enforces a materialization of the result, which has a positive effect on the performance of repetitive queries.