11 Replies Latest reply on Jan 18, 2018 4:57 PM by Dan Cory

    Performance problems using Tableau and Oracle

    Stephen Arthur

      Using Tableau 10.0, with an Oracle 12c database.

       

      The relatively simple Oracle SQL query is running through Tableau using a view (about 10 columns), but when we check the v$sql table for the SQL query the resultant query is 400 columns (with many repeating columns), and tying down our system resources.

       

      Any idea how Tableau creates queries like these from Oracle (or any other database)?

        • 1. Re: Performance problems using Tableau and Oracle
          Dan Cory

          I would recommend creating a performance recording to look at the SQL that Tableau is generating.

           

          Dan

          • 2. Re: Performance problems using Tableau and Oracle
            Stephen Arthur

            Dan,

             

            Thanks for the answer, I tried making a performance recording, but it did not return any SQL like what our DBA is saying was generated.

             

            It is generating what looks like XML code:

             

            Command

            "<?xml version='1.0' encoding='utf-8' ?>

             

            <sqlproxy>

               many lines of XML here

            </sqlproxy>

            "

            Is there a way to translate this into SQL code?

             

            Stephen

            • 3. Re: Performance problems using Tableau and Oracle
              Dan Cory

              Ah, you are using a published data source. There's no easy way to convert that XML back to the underlying SQL.

               

              I would try downloading the data source into a copy of your workbook and using replace data source to replace the published data source. Then you should be able to get a performance recording with the real SQL.

               

              Sorry it's a bit of a pain but we don't give you access to the SQL unless you have all the right permissions to do the steps above.

               

              Dan

              • 4. Re: Performance problems using Tableau and Oracle
                Stephen Arthur

                Dan,

                 

                I did the 'Performance Recording' directly off the website, as the end users would use the report, to replicate what they do.

                 

                I think I downloaded the workbook too, I am a little confused by what you are saying here "I would try downloading the data source into a copy of your workbook and using replace data source to replace the published data source. Then you should be able to get a performance recording with the real SQL."

                 

                "Sorry it's a bit of a pain but we don't give you access to the SQL unless you have all the right permissions to do the steps above."

                 

                What permissions do I need to view the SQL?

                 

                That's what I need!

                 

                Stephen

                • 5. Re: Performance problems using Tableau and Oracle
                  Dan Cory

                  You need to download the workbook and download the data source.

                  Then load the data source into the workbook in Tableau Desktop.

                  Then use "Replace Data Source" to swap out the original data source with the downloaded one.

                  Then you can do the performance recording in Tableau Desktop.

                   

                  Dan

                  • 6. Re: Performance problems using Tableau and Oracle
                    Stephen Arthur

                    Dan,

                     

                    Thanks, do you work for Tableau?

                     

                    We were able to get the Tableau generated SQL using the performance recording, as you mentioned, but the resulting SQL queries were 'normal' looking, not like what our DBA is showing us from TOAD.

                     

                    Our input query currently has 37 columns, but the resultant query from TOAD has as many as 485 columns, one column repeating 148 times (with no business reason, nor programming reason to do so).

                     

                    The query runs repeatedly starting at about 10 PM, and then in 80 minutes intervals according to the DBA's V$SESSION file, ending in 3 cycles, or as late as 6 AM.

                     

                    Are there any other ways to extract SQL code from Tableau?

                     

                    The Oracle USERNAME for the queries is APPDEV, is there anyway to tell in Tableau who might be initiating the query in Tableau?

                     

                    Thanks,

                     

                    Stephen

                    • 7. Re: Performance problems using Tableau and Oracle
                      Dan Cory

                      Yes, I do work for Tableau. You can click over to my profile to learn more about me.

                       

                      Tableau with published data sources sometimes doesn't generate the same queries as direct data sources, but we've been trying to fix those.

                       

                      Tableau Server generates detailed logs which contain the SQL queries and more information on where they came from. I don't encourage users to post their logs in a public forum, but Tableau Support can help you with this.

                       

                      If you are willing to post your performance recording and the bad queries here, I can have a look and see if I can suggest where the queries might be coming from.

                       

                      Dan

                      • 8. Re: Performance problems using Tableau and Oracle
                        Stephen Arthur

                        Dan,

                         

                        I will contact you via your work email, but I want to talk to my supervisor about what I can send you first.

                         

                        Another problem the DBA is reminding me of, is not only are there 400+ repetitive fields being generated (what he is seeing in TOAD), the WHERE statement is super complicated, either creating a large OR statement, or a huge CASE statement when you would normally use an IN statement if you typed it yourself.

                         

                        Stephen

                        • 9. Re: Performance problems using Tableau and Oracle
                          Dan Cory

                          Stephen - Tableau will generate relatively complicated WHERE clauses. We find that using IN clauses causes problems with large lists and so use ORs of ranges instead. We use CASE statements mostly for Tableau groups.

                           

                          Dan

                          • 10. Re: Performance problems using Tableau and Oracle
                            Stephen Arthur

                            Even though we have all those columns, there are only two tables used in the query, so the join itself seems pretty basic.

                             

                            Also, the way the query is presented to me from TOAD, it is uses all the column names as they were named in the Oracle data warehouse, not with any aliases that might have been created in Tableau.

                             

                            Have you encountered a problem like this before using Tableau?

                            • 11. Re: Performance problems using Tableau and Oracle
                              Dan Cory

                              Column names may get aliased in SQL, but generally not using the names assigned in Tableau. We want users to be able to use full Unicode characters in Tableau and many databases don't like that.

                               

                              We do sometimes see repetitive fields in SQL although we've tried hard to fix most of those. It's possible some of the fixes have gone in since the 10.0 version you are using. But I'm still surprised that you are seeing as many repeated fields as you describe.

                               

                              Dan