3 Replies Latest reply on Oct 3, 2016 4:50 AM by Russell Christopher

    TDE Loading From Disk into Memory

    Amar Chauhan

      Hi,

       

      Setup

      Extracts which have been setup on Desktop, using data sources from Stored Procedures and then scheduled to update every morning.

       

      Issue

      Refreshing my largest table via a SQL batch load takes 20 mins. A result which is expected due to the sheer size of the table (31.5gb). However, when I am attempting to do the same via a TDE, the Backgrounder Tasks for Exracts shows that it takes up to 3 hours for this Stored Procedure to update on Server. It is my understanding that a TDE is a compressed snapshot of data stored on disk first and then loaded into Memory? After comparing this refresh to the SQL batch load, I am 99.9% sure that it is the disk to memory part of this process which causing the refresh to take so long on a virtual box with 64GB RAM and 8 cores. Is there any reason why loading from disk to memory is inflating the load time into Tableau Server? Unfortunately I am unable to do an incremental load via this Stored Procedure.

       

      Hope you can help.

       

      Many Thanks,

      A

        • 1. Re: TDE Loading From Disk into Memory
          Jonathan Drummey

          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.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: TDE Loading From Disk into Memory
            Amar Chauhan

            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.

            • 3. Re: TDE Loading From Disk into Memory
              Russell Christopher

              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?