1 Reply Latest reply on Apr 12, 2013 1:44 PM by Alex Kerin

    Slow performance from ISNULL in WHERE Clause

    Chip Lynch

      NO question here, I have a workaround... I don't know why Tableau does this, but I wanted to share since I couldn't find another reference to the problem...

       

      First, I'm using Tableau 8.0 on SQL Server 2012.  No idea if this happens anywhere else...

       

      I have a calculation called "Group or Provider Name":  IFNULL([GROUP_NAME], [PROVIDER_NAME])

       

      I use it fairly frequently in charts... basically if someone is part of a group, we want to show the group information, otherwise we just show their individual information.  No worries and this works great most of the time.

       

      Sometimes, though, and this happens more frequently when running on the web server rather than on the desktop version, Tableau will basically hang on a report that is normally a 15-second report.  I've waited 15+ minutes with nothing.

       

      The REASON is this:  Tableau submitted a query to SQL Server doing this:

       

      SELECT TOP 1 ISNULL([SPGA].[GROUP_NAME], [SPD].[PROVIDER_NAME]) AS [Calculation_9860412105917202]

      FROM [Facts].[CLIF] [CLIF]

        INNER JOIN [Dimensions].[SPD] [SPD] ON ([CLIF].[SERVICE_PROVIDER_T1_SKEY] = [SPD].[PROVIDER_T1_SKEY])

        LEFT JOIN [Dimensions].[SPGA] [SPGA] ON ([CLIF].[SERVICE_PROVIDER_T2_SKEY] = [SPGA].[PROVIDER_T2_SKEY])

      WHERE (ISNULL([SPGA].[GROUP_NAME], [SPD].[PROVIDER_NAME]) IS NULL)

       

      I added some bold and underline for emphasis... Tableau is requesting a single record back using the ISNULL function (not IFNULL, which I used in my calculation -- subtle important difference).  Apparently Tableau wants to check if the result set will ever be NULL (note the IS NULL [with space] in the where clause after the ISNULL [no space] function). This single query destroys my performance.

       

      The SOLUTION, is to ensure that there is a default value and that the final result of the IFNULL can never be NULL... that is, I changed the calculation to:

      IFNULL(IFNULL([GROUP_NAME], [PROVIDER_NAME]), 'Unknown')

       

      So it will return 'Unknown' instead of NULL if both names are missing (a circumstance which never happens in the dataset, by the way).  Now things are fast again.  I don't know if Tableau stopped running the silly query entirely or just rewrote it so SQL Server can just optimize it better by shortcutting the WHERE clause, but in either case performance is good again.

       

      Thought you all should know.