4 Replies Latest reply on Oct 19, 2018 7:16 AM by Erika Young

    Function in SQL or Tableau?

    mark.welsh

      Can someone help me understand the performance implications of using a function such as COALESCE within Custom SQL for an Extract vs. creating the function in a Tableau calculated field?  I would assume since it's an extract it would be faster to do in Custom SQL...I also assume the the SQL function is executed when the extract is created and the result is stored at that time rather than calculated within a View.  Is that correct?

       

      Thanks!

        • 1. Re: Function in SQL or Tableau?
          Hari Ankem

          Yes, you are correct. Any calculations that are done on Tableau, are done on the fly when we open a dashboard/worksheet. So, more the calculations, and more complex they are, the performance of the dashboard will degrade.

          • 2. Re: Function in SQL or Tableau?
            Charles Schaefer

            I would actually argue that using Custom SQL will be more of a performance hit than writing calculations in Tableau.

             

            When you write a calculation in Custom SQL - you force every query Tableau sends to the database to SELECT FROM that exact statement. So if your Custom SQL statement uses COALESCE, the query is going to COALESCE every time you drag and drop in Tableau, regardless of whether that calculation is even part of your analysis.

             

            (This is assuming you are connecting live. If you are using an extract then it should be blazing fast.)

             

            However, if you can materialize your calculation in a table or view in your database, you can query that table or view directly by connecting to it using Tableau's native connector capability (not custom SQL) and it should be as fast as your DB will allow.

             

            If you do write the calculation in Tableau rather than in the database, it will still likely operate in the database (exception: Table Calculations) but it will only execute when you use that field in a query, so it will almost certainly be faster than Custom SQL. But it will sometimes be slower than materializing the result of that calculation in a table as previously mentioned.

             

            re: COALESCE - in Tableau, you'll probably use IFNULL or IF ... ISNULL to replicate the same functionality.

             

            Hope that helps.

            2 of 2 people found this helpful
            • 3. Re: Function in SQL or Tableau?
              mark.welsh

              Thanks guys, very helpful and exactly the information I needed. 

              • 4. Re: Function in SQL or Tableau?
                Erika Young

                Charles, just to clarify your statement (This is assuming you are connecting live. If you are using an extract then it should be blazing fast.).

                 

                Does this mean that, if I use extract, it doesn't matter in terms of performance where (custom sql or tableau) I write the calculation? Thanks.