8 Replies Latest reply on May 24, 2011 5:21 PM by Robert Morton

    Very slow SQL query with Oracle connection

    Michael Mixon

      Hi,

       

      I am connecting to an Oracle database and have a SQL query provided to me by one of our DBAs.  The query takes an enormously long time to execute inside of Tableau (sometimes 3 hours) whereas the same query returns results in 15-20 minutes using an application like Toad.

       

      Our DBA noticed that Tableau appears to place a "SQL wrapper" around the original SQL, and I was wondering if that might be what would cause this query to run significantly slower in Tableau vs. another application.  We'd like to get this query to perform better (having a monster SQL query running for 3 hours doesn't make our data center very happy), so any ideas would be appreciated.

       

      I'm pasting the SQL statement below, specifically the additional "wrapper" Tableau is adding.

       

      SELECT *

      FROM (SELECT "TableauSQL"."SALES_ORDER_LINE_FREIGHT_TERMS"

                      AS "SALES_ORDER_LINE_FREIGHT_TERMS"

            ...ORIGINAL SQL STARTS HERE:

            FROM (SELECT                                            /*+ full(ois) */

                        dd.fiscal_year,

                        dd.fiscal_period_name,

                        dd.fiscal_quarter,

                        dd.fiscal_week,

                        dd.fiscal_week_age,

                        dd.fiscal_quarter_age,

                        ....REST OF SQL query...too long to paste the entire thing here...

                ) "TableauSQL"

            WHERE "TableauSQL"."SALES_ORDER_LINE_FREIGHT_TERMS" IS NULL)

      WHERE ROWNUM <= 1

       

      Thanks,

       

      -Mike

        • 1. Re: Very slow SQL query with Oracle connection
          Robert Morton

          Hi Mike,

           

          Yes, custom SQL is often the cause of poor query performance because many database systems don't handle subquery expressions very well.  The reason that Tableau wraps the custom SQL expression as a subquery is so we can add analytical clauses such as HAVING, ORDER BY, WHERE, etc., without having to determine how to merge this into the existing syntax expressed by the user's custom SQL.

           

          What is driving your use of custom SQL in this case?  It is simply the query optimizer hints that you have embedded, e.g.

          /*+ full(ois) */
          ?  Are you able instead to create a normal single- or multi-table connection in Tableau and avoid performance problems?

           

          -Robert

          • 2. Re: Very slow SQL query with Oracle connection
            Michael Mixon

            Hi Robert,

             

            The query is actually a join of several tables, as you can see below.  I suspect this would be difficult to achieve in Tableau with just multi-table joins.

             

            from sma.order_invoice_shipment_Fact ois,

                  dim.date_dim dd,

                  dim.customer_dim cd,

                  dim.customer_dim bcd,

                  DIM.SALES_TERRITORY_DIM ST,

                  FIDW.PARENT_TO_CHILD_PRODUCT_DIM_VW pd,

                  DIM.SUB_INV_LOCATION_DIM si,

                  DIM.SHIPMENT_DIM SD,

                  (select /*+ parallel(s 24) */shipment_key,

                    shipment_milestone_key,

                      MIN(CASE WHEN milestone_num = 1 then to_date(MILESTONE_DATE_KEY,'yyyymmdd')+ (MILESTONE_TIME_KEY-1)/86440 end ) PICKUP_AT_ORIGIN_DATE,

                      MIN(CASE WHEN milestone_num = 2 then to_date(MILESTONE_DATE_KEY,'yyyymmdd')+ (MILESTONE_TIME_KEY-1)/86440 end ) DEPART_AT_ORIGIN_DATE,

                      MIN(CASE WHEN milestone_num = 3 then to_date(MILESTONE_DATE_KEY,'yyyymmdd')+ (MILESTONE_TIME_KEY-1)/86440 end ) ARRIVAL_DEST_AIRPORT_DATE,

                      MIN(CASE WHEN milestone_num = 4 then to_date(MILESTONE_DATE_KEY,'yyyymmdd')+ (MILESTONE_TIME_KEY-1)/86440 end ) DEST_CUSTOMS_CLEAR_DATE,

                      MIN(CASE WHEN milestone_num = 5 then to_date(MILESTONE_DATE_KEY,'yyyymmdd')+ (MILESTONE_TIME_KEY-1)/86440 end ) PICKED_UP_FINAL_DEL_DATE,

                      MIN(CASE WHEN milestone_num = 6 then to_date(MILESTONE_DATE_KEY,'yyyymmdd')+ (MILESTONE_TIME_KEY-1)/86440 end ) POD_DATE

                  from QIS.SHIPMENT_MILESTONE_FACT s

                  where milestone_type = 'M'

                  group by shipment_key,

                                shipment_milestone_key) SMF,

                  DIM.shipment_milestone_dim smd,

                  DIM.CUSTOMER_CORP_DIM CCD,

                  dim.country_dim ctd

            • 3. Re: Very slow SQL query with Oracle connection
              Richard Leeke

              Would it be an option to generate an extract?  You might find that just generating an extract without any of the additional clauses added by the analysis allows the query to run at its normal speed.  Then your analysis would be quick.  Obviously the data wouldn't be current, so you might have to refresh very frequently - but it could just be more viable than 3 hours every time you need to re-query as part of your analysis.

               

              Just a thought...

              • 4. Re: Very slow SQL query with Oracle connection
                Michael Mixon

                Hi Richard,

                 

                Actually, the time is in the creation of the extract.  I generally never perform any analysis on a live connection, as it is too slow.  So I always take an extract and then send the updated dashboard to the users.  I simply select 'Refresh Extract' and then wait 2-3 hours for the extract to get refreshed.

                • 5. Re: Very slow SQL query with Oracle connection
                  Robert Morton

                  Hi Mike,

                   

                  Some database systems operate better with views than subqueries, while others treat subqueries and views identically.  You may wish to experiment with a non-materialized view to see if your Oracle system can better optimize Tableau queries against it.  On a related note, is your Oracle system oriented towards transactions (OLTP) or analysis (OLAP)?

                   

                  -Robert

                  • 6. Re: Very slow SQL query with Oracle connection
                    Richard Leeke

                    Hi Mike

                     

                    I should have guessed you were already using an extract - 3 hours every time you drag a field onto a shelf wouldn't work for anyone.

                     

                    Another thought if Robert's view suggestion doesn't help is that you could use a 2 step process for refreshing the extract - by actually creating a materialised view in Oracle and refreshing that first at the Oracle level and then refreshing your Tableau extract.  Or some other way of storing your intermediate results.

                     

                    I guess you may already be doing that, too.

                    • 7. Re: Very slow SQL query with Oracle connection

                      Robert Morton:  "...On a related note, is your Oracle system oriented towards transactions (OLTP) or analysis (OLAP)?"

                       

                      How do these affect Tableau?  Is one preferred over the other for Tableau?

                      • 8. Re: Very slow SQL query with Oracle connection
                        Robert Morton

                        Yes, OLAP systems are tuned to handle complex analytical queries and very few inserts. OLTP systems are designed for high concurrent rates of data insertion, which necessarily impacts one's ability to run complex analytical queries.

                        -Robert