1 Reply Latest reply on Oct 27, 2017 4:57 PM by Andrew Macey

    How does Tableau Server handle memory allocation when every string column in the data is varchar(256)?

    Susan Klug

      Hi,

       

      We are evaluating a move to Snowflake and are considering using all string columns as varchar(max) because Snowflake will only allocate the memory required, not the max.

      For example in Snowflake is that you can make every string column in the database a VARCHAR(256) and if you only put “Hello World” in it, it will only take up the space of a VARCHAR(11).

       

      We are concerned that if/when Tableau does a query against a column that is defined that way,

      Tableau might allocate a ton of space under the covers because it is anticipating a lot of data… It could cause performance problems…

       

      Does anyone know what Tableau will do with memory allocation in this case?

       

      I am planning to do some testing with perfMon and the postgreSQL data, but as yet do not have permission on the Tableau Server box.

       

      Thanks,


      Susan

        • 1. Re: How does Tableau Server handle memory allocation when every string column in the data is varchar(256)?
          Andrew Macey

          I'd like to see the results of a memory test, but can confirm a (smallish but unexpected) difference in extract sizes for the same data in this Oracle test.

           

          SQL> -- Oracle

          SQL> create or replace view v_varchar_10 as

            2  select CAST( level AS varchar2(10) ) the_data from dual

            3  connect by level <= 1000000;

          View created.

           

          SQL> create or replace view v_varchar_4000 as

            2  select CAST( level AS varchar2(4000) ) the_data from dual

            3  connect by level <= 1000000;

          View created.

           

          SQL> desc v_varchar_10;

          Name                                      Null?    Type

          ----------------------------------------- -------- ----------------------------

          THE_DATA                                           VARCHAR2(10)

           

          SQL> desc v_varchar_4000;

          Name                                      Null?    Type

          ----------------------------------------- -------- ----------------------------

          THE_DATA                                           VARCHAR2(4000)

           

          SQL> select count(*), avg(length(the_data)) from v_varchar_10;

            COUNT(*) AVG(LENGTH(THE_DATA))

          ---------- ---------------------

             1000000              5.888896

           

          SQL> select count(*), avg(length(the_data)) from v_varchar_4000;

            COUNT(*) AVG(LENGTH(THE_DATA))

          ---------- ---------------------

             1000000              5.888896

           

          In Tableau desktop 10.0, extract both sources to .TDE.

           

          C:\Temp>dir v_varchar*.tde

          Volume in drive C is xxx

          Volume Serial Number is yyy

          Directory of C:\Temp

          10/27/2017  04:43 PM         7,311,962 V_VARCHAR_10.tde

          10/27/2017  04:44 PM         8,311,978 V_VARCHAR_4000.tde