First, you can learn more about why Tableau generates the kinds of queries it does (including those involving temp tables) from a talk presented at our 2011 Customer Conference. View the recorded talk or the slides for "Tuning Tableau and Your Database for Great Performance", in the Advanced section here: http://www.tableausoftware.com/tcc11-sessions#advanced
Complex filters can lead Tableau to stage queries in a temporary table for functionality or performance. In some cases, the query expression in the WHERE clause simply becomes too large for the database query processor to handle. In other cases, a large WHERE clause simply leads to poor performance. Tableau will instead join against a temporary table containing the values to be filtered. As described in the talk I linked, you should examine your action filters to determine if they are expressing a collection of discrete values instead of a more succinct range of values. While temporary tables may help improve performance for filtering discrete items, they introduce overhead for populating the tables, they may not have good (if any) indexes, and they tend to be poorly supported by query optimizers of MPP databases. You may find immediate improvements by looking for opportunities to use range filters if possible.
Does this help?
Hi Robert -- yes this helps, thanks very much for the link, great information there. One question I have -- is the workbook that Matt showed in the video available? I wasn't sure exactly how the embedded list was added, I think that might be helpful to use in cases where we know the list of aggregations that we might encounter ahead of time.
In our case, we are working with a very large pool of time series data that is currently accessed via a SQL Server view in the data connection as I described in the first post. Our action filtering is around identifiers for points that we want to view, and then our selection of the time series data is also limited by a range of dates selected through a quick filter (relative date), and then we add quick filters for other attributes, like the day of week and time of day. So, we have a mix of ranges in a quick filter (the actual dates) and discrete values (point IDs) in actions, and discrete values in additional quick filters. It seems to be when we add the additional quick filters, that is when we start getting the temp database growth, and that is consistent with conditions you describe above in generating a complex WHERE clause. So, I think I understand what drives the temp table creation now -- thank you.