4 Replies Latest reply on Oct 9, 2013 12:36 PM by john.radin

    Performance Optimization for Large UNION Query

    john.radin

      Hello All-

       

      I am trying to accomplish a UNION between two large Oracle tables (several million records each) based on their relevant shared dimensions/fields (about 70).  For background and professional context, one table is institutional (inpatient) claims and the other is professional (outpatient), so the purpose of the exercise is to ultimately summarize some basics (claims counts, financials, etc.) by plan sponsor by month/year.  I only have intemittant access to our Oracle data warehouse where I ultimately want to make a consolidated table as part of our load process but until then what are my options?  What would be the most efficient way to go about this?

       

      Custom SQL?  Creating a TDE locally (how long, etc.)?  I have been trying these options.

       

      Any suggestions on how to best to accomplish with Tableau functionality?

       

      Who else is looking forward to some of the new 8.1 and 8.2 features ?

       

      Cheers,

      John

        • 1. Re: Performance Optimization for Large UNION Query
          Robert Morton

          Hi John,

           

          In general when working with Oracle, you will find that performance is much better if you turn your Custom SQL connection into a VIEW on the database. You can then use Tableau to connect directly to the view, which should show up in the single-table connection list. The reason for this performance difference is that a Custom SQL connection relies on subqueries, which Oracle does a very poor job of optimizing; in contrast, a view is essentially a named, persisted query that informs Oracle of your intent to perform that work repeatedly. Even the simplest VIEW (not a materialized one) can have much better performance than a Custom SQL connection with the same query text.

           

          I hope this helps,

          Robert

          • 2. Re: Performance Optimization for Large UNION Query
            Shawn Wallwork

            The reason for this performance difference is that a Custom SQL connection relies on subqueries, which Oracle does a very poor job of optimizing;

            Robert is this true of other databases? MS SQL for instance? Thanks,

             

            --Shawn

            • 3. Re: Performance Optimization for Large UNION Query
              Robert Morton

              Yes, SQL Server will see a performance gain from this technique, though it's not as dramatic as with Oracle (because Microsoft SQL Server has an excellent query optimizer).

               

              MySQL is like Oracle in having a dramatic performance gain from a VIEW vs. a subquery.

               

              Beyond that, I don't have a comprehensive list.

               

              -Robert

              • 4. Re: Performance Optimization for Large UNION Query
                john.radin

                Hello All-

                 

                Thank you for your advice, Robert.  I created a dedicated view of the union of the two tables, and it now has tolerable performance (still much slower than accessing the table independently).  It seems like there is actually a lot of popular debate on whether creating dedicated views actually speeds up performance in reading through some stack overflow discussions on the topic.  In my case, it appears to have certainly helped.  I still might end up creating a consolidated table.  Not very elegant or good for the data model, but it might be needed eventually.

                 

                Robert Morton

                 

                Thanks,

                John