1 of 1 people found this helpful
You wrote, "It is my understanding that a TDE is a compressed snapshot of data stored on disk first and then loaded into Memory?" I can see how that understanding might arise but that is not accurate.
A Tableau Data Extract is a compressed, sorted, indexed, memory mapped, columnar data store. It is *not* a true in-memory source, thought it's set up to be very quickly loaded into memory as necessary based on what is required for the Tableau views that access it. So the difference between a SQL batch load that takes 20 minutes and a TDE refresh that takes up to 3 hours is at least partially due to the fact that the SQL batch load is "just" streaming data to disk while the TDE refresh is compressing, sorting, indexing, and memory mapping the data and finally writing the resulting TDE to disk. Other factors include disk I/O, CPU load, available memory, etc.
Thanks for your response Jonathan. Are there any techniques which could speed up the TDE? Or can you suggest a better way to extract data from a SQL database into a Tableau? The example above is our largest table and it is ever growing. As a priority, our aim is reduce the refresh time for this as this step in the Daily Schedule as it is taking too long and doesn't serve the purpose of up to date self serving data within the business. Any tips will be appreciated.
A couple thoughts:
You mention a stored procedure, You access the data with Tableau using a sproc in SQL Server? That will be "doubly slow" because the data gets landed in a #temp table inside SQL Server before it is shipped (piece by piece) to the Tableau machine. If you are using a sproc, you are not only copying all the data over your network and landing to disk before processing, you are duplicating it inside SQL Server before the copy.
Here's how I would try to baseline this: If you execute some SQL (using Query Analyzer) which executes your sproc and loads a #temp table with the results, then you immediately SELECT * from that #temp table (which is not what Tableau really does), and bring all that data from the SQL Server across the wire to your client machine...how long does it take? Until this process is complete, Tableau doesn't even start to process said data into an extract.
Generally the SELECT / COPY is what takes the longest, Is there any way to access the tables directly without a stored procedure?