3 Replies Latest reply on Oct 24, 2013 11:59 AM by Dan Cory

    Tableau repackaging IF Statements?

    choun sourathathone

      Is anyone familiar with how tableau repackages IF statements for queries against SQL Server? this query used to run in 90 seconds by now pegs the server and completes somewhere north of 35 minutes. IT admin suggests it might be a loop caused by Tableau's transformed IF statement (at bottom). I don't have a scrubbed workbook to share yet.

       

      Thanks.

       

      ORIGINAL CALC:

      if contains ([AccountName], 'I2') and contains ([AccountName], 'Engineering') then 'I2 Engineering'

      elseif contains ([status], 'beta') then 'Beta / Eval'

      elseif contains ([status], 'I2 Owned') then 'I2 Owned'

      elseif contains ([status], 'Loaner') then 'Loaner'

      elseif contains ([status], 'purchase') then 'Purchased'

      elseif contains ([status], 'Oracle-Deinstall') then 'Oracle-Deinstall'

      elseif contains ([status], 'Oracle-Install') then 'Oracle-Install'

      elseif contains ([status], 'OEM') then 'MISC'

      elseif contains ([status], 'returned') then 'Returned'

      elseif contains ([status], 'rma') then 'RMA'

      else [status]

      end

       

      TABLEAU TRANSFORMED CALC:

      SELECT TOP 1/*-Most likely causing nested loops from optimizer*/

      ( CASE

                       WHEN  ((CASE

                                    WHEN Len('I2') = 0 THEN 1

                                    ELSE Charindex('I2',

                                         [table1].[accountname])

                                END )> 0

                                          AND ( ( CASE

                                        WHEN Len('Engineering') = 0 THEN 1

                                        ELSE Charindex('Engineering',

                                  [table1].[accountname])

                                      END ) > 0 ) ) THEN 'I2 Engineering'

                       WHEN ( ( CASE

                                  WHEN Len('beta') = 0 THEN 1

                                  ELSE Charindex('beta',

                                       [table1].[status])

                                END ) > 0 ) THEN 'Beta / Eval'

                       WHEN ( ( CASE

                                  WHEN Len('I2 Owned') = 0 THEN 1

                                  ELSE Charindex('I2 Owned',

                                       [table1].[status])

                                END ) > 0 ) THEN 'I2 Owned'

                       WHEN ( ( CASE

                                  WHEN Len('Loaner') = 0 THEN 1

                                  ELSE Charindex('Loaner',

                                       [table1].[status])

                                END ) > 0 ) THEN 'Loaner'

                       WHEN ( ( CASE

                                  WHEN Len('purchase') = 0 THEN 1

                                  ELSE Charindex('purchase',

                                       [table1].[status])

                                END ) > 0 ) THEN 'Purchased'

                       WHEN ( ( CASE

                                  WHEN Len('Oracle-Deinstall') = 0 THEN 1

                                  ELSE Charindex('Oracle-Deinstall',

                                       [table1].[status])

                                END ) > 0 ) THEN 'Oracle-Deinstall'

                       WHEN ( ( CASE

                                  WHEN Len('Oracle-Install') = 0 THEN 1

                                  ELSE Charindex('Oracle-Install',

                                       [table1].[status])

                                END ) > 0 ) THEN 'Oracle-Install'

                       WHEN ( ( CASE

                                  WHEN Len('OEM') = 0 THEN 1

                                  ELSE Charindex('OEM',

                                       [table1].[status])

                                END ) > 0 ) THEN 'MISC'

                       WHEN ( ( CASE

                                  WHEN Len('returned') = 0 THEN 1

                                  ELSE Charindex('returned',

                                       [table1].[status])

                                END ) > 0 ) THEN 'Returned'

                       WHEN ( ( CASE

                                  WHEN Len('rma') = 0 THEN 1

                                  ELSE Charindex('rma',

                                       [table1].[status])

                                END ) > 0 ) THEN 'RMA'

                       ELSE [table1].[status]

                    END ) AS [Calculation_5810619095028083]

      FROM   [dbo].[table1]

        • 1. Re: Tableau repackaging IF Statements?
          Dan Cory

          There are two things happening here - the if / elseif / elseif is being turned into a case and the CONTAINS is being expanded out a bit for compatibility. I would think the SQL Server optimizer could handle either of these, although if anything it's the CONTAINS not the CASE that is the problem. The best way to investigate is use the Show Plan features in SQL Server to see exactly what it is doing in Tableau's query and your query. Your IT admin may have to help you with this.

           

          Dan

          • 2. Re: Tableau repackaging IF Statements?
            choun sourathathone

            Thanks for the quick reply, Dan. You were right about the CONTAINS statement. I removed all calcs and tested one calc with a single line CONTAINS statement which produced wildly different latencies depending on which string was used. We're looking into options, but any other suggestions you can think of would be helpful.

             

            if contains ([field], 'DEV') then 'Other'

            elseif contains ([field], 'BETA') then 'Other'

            else [field]

            end

            • 3. Re: Tableau repackaging IF Statements?
              Dan Cory

              I would use the Show Plan features in SQL Server to see how your query is getting processed by SQL Server. It's possible you will find some indexes or other changes that will speed the query up. You could also use the RAWSQL function to have Tableau generate exactly the SQL you want. You could contact support if you are certain that Tableau is generating the wrong SQL, but they will want you to use Show Plan anyway.

               

              Thanks,

              Dan