Test Your Data and Use Extracts
You probably have no control over the hardware, processing power, and disk space that your database runs on. Or how many people access the database at any given time, how many services are running, or how robust the network is. But you can test the raw performance of the data before you begin to build a workbook, so that you understand the environment you’re working in. And there are things you can do to affect the performance of the workbooks you create, starting with how you choose to connect to your data: the drivers you use, and whether your connection type is live or extract.
Test as close to the data as possible
A good way to test the raw performance of the data is to install Tableau Desktop on the computer where the data resides and to run some queries. This eliminates external factors such as network bandwidth and latency from performance results, and help you to understand the raw performance of the query in the data source.
Additionally, you can use the localhost name for the data instead of the DNS name to help determine if environmental factors such as slow name resolution or proxy servers are slowing performance.
Connect with native database drivers
When you connect to data with native database drivers, you will often experience significantly faster performance than when you connect to the same data with ODBC drivers. When you use native database drivers, make sure that you’re using the latest version. Database vendors are always working to improve their drivers. For a list of drivers, see Drivers and Activation on the Tableau website.
Work with extracts instead of live data
Depending on your data, you can choose between a live or extract connection on the data source page. A live connection is a direct connection to your data. A Tableau data extract is a compressed snapshot of data stored locally and loaded into memory as required to render a Tableau visualization. Extracts are designed to use all parts of your computer’s memory optimally.
There are several reasons to use an extract, but the main performance-related reason is if your query execution is slow. The extract data format is designed to provide a fast response to analytic queries. In this case, you can think of the extract as a query acceleration cache.
For more information about Tableau extracts, see Extract Your Data. Another reference is a three-part blog post about extracts that starts with the first post, Understanding Tableau Data Extracts.
Reduce the amount of data
When you create an extract, use filters to exclude data that you don't need. Also, ask yourself if you need all of the records in a data source, or if you can limit the extract to a representative sample. For more information, see Extract Your Data.
Hide unused fields
Hidden fields are not included when you create an extract. Use the Hide All Unused Fields option to hide unnecessary fields before you create an extract. This makes the extract smaller, which improves performance. For more information, see Hide or Unhide Fields.
Optimize extracts
The Compute Calculations Now option materializes calculations in your extract, meaning that certain calculations are computed in advance and their values are stored in the extract. Depending on the complexity of the calculations used in your extract, this can potentially speed up future queries.
To materialize calculations, select a data source on the Data menu and then select Extract > Compute Calculations Now. For more information, see Materialize Calculations in Your Extracts.
Use extracts for file-based data
In general it’s best practice to import file-based data—text files such as CSV, Microsoft Excel spreadsheets, and Microsoft Access files, as well as statistical files like SPSS, SAS, and R—into Tableau. This makes queries perform much faster and also results in a much smaller file to store the data values.
If your data is a large text or Excel file, using an extract not only improves performance but also makes more functionality available to you. Note that if you connect Tableau to a large text file, you will be prompted to extract the data if Tableau discovers that the file is too large to perform well.
Avoid using custom SQL
In most cases, custom SQL runs slower than queries created by Tableau. Tableau cannot perform query optimizations on custom SQL. But in cases where you must use custom SQL, use an extract so that the query runs only once. For more information, see Connect to a Custom SQL Query.