1 Reply Latest reply on Aug 9, 2016 11:00 PM by Dmitry Chirkov

    SQL Query Performance

    Yeleli Burcak

      Hello everyone,

       

      I connect to Microsoft SQL Server 2008 with Tableau.

       

      I have several dimensional calculated fields. I put some of them to the Filter and some of them to Row/Column.

      To prepare a basic calculation like summing two Measure fields, Tableau send an SQL query to the server, where there are "CASE WHEN" expressions in "WHERE" and "GROUP BY" clauses.

      The calculated fields are done by IF ELSE statements.

       

      Our database admin says that having "case when" in the "where / group by" creates huge performance issues on the SQL server side. The SQL server's CPU and RAM are around 100%. We cannot get the results of the queries.

       

      Two questions:

      1) Is this query building format normal for Tableau? Can this be the cause for the performance problem?

      2) Is it possible not to include these CASE WHEN expressions in the database side, rather do the calculated field calcs on the Tableau side?

       

      Other info:

      I join multiple tables in the Tableau data source interface.

      Calculated fields are row level, not aggregated.

      I use live connection, not extract.

        • 1. Re: SQL Query Performance
          Dmitry Chirkov

          Yes, it's perfectly normal.

           

          The way you are building the viz (dimensions on rows and columns) defines your aggregation (i.e. sales by region) and this goes into GROUP BY clauses. IF/THEN calculation is usually turned into CASE, especially if it has branching (nested IF/ELSE or ELSEIF).

           

          Yeah you can get rid of those but then you would have to not use filters and uncheck "Aggregate Measures" under Analysis menu. Not sure if you would be able to get any useful information our of such viz though :/

           

          Did your admin happen to suggest what would be the equivalent query that would produce same results but would be faster?

           

          What I is that your database can't really handle Tableau load which happens. Going forward this should be a collaboration between you and DBA to potentially make necessary changes in database (indexes, normalization etc). On your side you could avoid complex string operations (CONTAINS(), wildcard search etc). Teamwork!