3 Replies Latest reply on Apr 4, 2017 1:12 PM by Karthik Venkatachalam

    Tableau string functions not optimized for Oracle

    Zach Leber

      Tableau's SQL generator produces exponentially complex Oracle SQL case statements when concatenating several strings using the + operator in Tableau. Tableau also uses COALESCE instead of NVL to implement its IFNULL() function. The combined result can be extremely slow execution against a large Oracle database. My workbook on Server was taking 30 seconds to update instead of 1 second as it should have. By using RAWSQL to call Oracle functions and operators directly, we can solve this problem while still allowing ad hoc calculated fields to be created and used.

       

      Tableau Calculated FieldResulting SQLTime to Execute
      IFNULL([Seq Project], [Research Project Id])+":"+[Product Order Sample]+":"+USERNAME()

      SELECT (CASE WHEN (CASE WHEN (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) IS NULL OR ':' IS NULL THEN NULL ELSE (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) || ':' END) IS NULL OR 'zleber' IS NULL THEN NULL ELSE (CASE WHEN (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) IS NULL OR ':' IS NULL THEN NULL ELSE (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) || ':' END) || 'zleber' END) AS "Calculation_243672893310484070"

      FROM "COGNOS"."POOLING_CALCULATOR" "POOLING_CALCULATOR"

      WHERE ("POOLING_CALCULATOR"."FLOWCELL_BARCODE" = 'H7LMCALXX')

      GROUP BY (CASE WHEN (CASE WHEN (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) IS NULL OR ':' IS NULL THEN NULL ELSE (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) || ':' END) IS NULL OR 'zleber' IS NULL THEN NULL ELSE (CASE WHEN (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) IS NULL OR ':' IS NULL THEN NULL ELSE (CASE WHEN (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) IS NULL OR "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" IS NULL THEN NULL ELSE (CASE WHEN COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") IS NULL OR ':' IS NULL THEN NULL ELSE COALESCE("POOLING_CALCULATOR"."SEQ_PROJECT", "POOLING_CALCULATOR"."RESEARCH_PROJECT_ID") || ':' END) || "POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE" END) || ':' END) || 'zleber' END)

      >30 seconds
      RAWSQL_STR("%1||'-'||%2||'-'||%3", RAWSQL_STR("NVL(%1,%2)", [Seq Project], [Research Project Id]), [Product Order Sample], USERNAME())

      SELECT ((NVL("POOLING_CALCULATOR"."SEQ_PROJECT","POOLING_CALCULATOR"."RESEARCH_PROJECT_ID"))||'-'||"POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE"||'-'||'zleber') AS "Calculation_243672893310484070"

      FROM "COGNOS"."POOLING_CALCULATOR" "POOLING_CALCULATOR"

      WHERE ("POOLING_CALCULATOR"."FLOWCELL_BARCODE" = 'H7LMCALXX')

      GROUP BY ((NVL("POOLING_CALCULATOR"."SEQ_PROJECT","POOLING_CALCULATOR"."RESEARCH_PROJECT_ID"))||'-'||"POOLING_CALCULATOR"."PRODUCT_ORDER_SAMPLE"||'-'||'zleber')

      <1 second
        • 1. Re: Tableau string functions not optimized for Oracle
          Karthik Venkatachalam

          Thanks for sharing Zach. I am facing a similar problem as well. where, I would have to show only records with expense balance. Dont want to show ones with Zero balance. If I try to write a field as below, then it produces some outrageous, long running, SQL that doesn't scale, and puts huge burden on DB (SQL Server 2014).

           

          IF SUM(expense_balance) <> 0 then 'SHOW'

          ELSE 'DONT SHOW'

          END

           

          I hope someone can offer a suggestion for this.

          • 2. Re: Tableau string functions not optimized for Oracle
            Zach Leber

            I think that's a different optimization problem, but one which I have some recent experience. I am pulling down millions of rows of expense data from BigQuery, but many of the rows have cost=0, so I added "WHERE cost<>0" to my Custom SQL query. That's a non-aggregate row-level filter. To do the same inside the Tableau interface, try creating a boolean calculated field called [zero_balance], set to [expense_balance]=0, and put that on your filter shelf, set to False.

            • 3. Re: Tableau string functions not optimized for Oracle
              Karthik Venkatachalam

              Yes, I have tried the Boolean as well. But it acts same. I added the boolean to filter and gave user a radio button to hide or show Non-zero values.But it generates the random crazy sql.

              I would still need the zero records though. Unlike your case, where you could shut it out all together on the Custom SQL.

               

              Tableau research folks need to check why certain weird SQL gets generated.

              Richard Wesley