5 Replies Latest reply on Jun 19, 2018 2:17 AM by philip.watkinson

    Tableau takes a long time to execute a query

    ba s

      Hi,

       

      I have a database in microsoft Access which i have exported all data to MySQL. I have connected Tableau to MySQL, and joined 3 tables each having a size of 900MB, 166MB, 800MB. I will say import all data, execution of query takes more than 45 min, Any idea on this since they have portrayed tableau as big data tool.

        • 1. Re: Tableau takes a long time to execute a query
          Andrew Chiu

          You can try using the following steps to help diagnose which part(s) of the data extraction process takes longest:

           

          1. From Tableau Desktop, choose Help > Start Performance Recording.

          2. Right-click the Data Connection you have already set up, choose Extract > Refresh.

          3. Wait the 45 minutes or so for the extract to be refreshed.

          4. Choose Help > Stop Performance Recording.

          5. A new window will open with your performance recording, showing a breakdown of your data extraction process.

          • 2. Re: Tableau takes a long time to execute a query
            Toby Erkson

            You haven't provided enough information.  Are you talking about Tableau Server or Desktop?  What version?  Are you building an extract or using a live connection or ???  How is the data being pulled, Custom SQL or Multiple Tables or separate data sources or ???  We need details.

             

            Tableau IS a big data tool but the person building the report(s) affects how fast it can move.

            • 3. Re: Tableau takes a long time to execute a query
              Amit K

              Apparently many people are having this problem with specifically large data sources. When I use the performance monitor, all it does is keep executing the query for 900 seconds and then terminate it with the message 'Operation Timed Out'.

              In my case, I am using Tableau Desktop 9.3 connected to data source published on Tableau server 9.3. The data source is a live connection, not an extract. The data source is simply a join statements of 1 fact table and about 9 dimension tables. Data traffic in the database: about 500,000 rows per day.

               

              For those who don't have Tableau 9.3, I am also attaching an image. If I click on either of the bars, I do not get any query.

               

              Any help is highly appreciated.

               

              • 4. Re: Tableau takes a long time to execute a query
                Tim Daunch

                There is a common misperception among BI tools, Tableau included, that  "<name of BI tool> is taking a long time to run a query". BI tools do not run queries - databases do. ALL BI tools send a query (SQL or otherwise) to a data source (relational, cube, etc.) and wait for a response. When you see an "operation timed out" message, this is almost always the database closing the connection, not Tableau. That said, I have seen Tableau building TEMP tables in certain data sources in order to satisfy the query (MSTR does this on the fly as well). This behavior is very specific to 1) the query itself, 2) the structure of the data source (tables, join types, etc.), and the brand of database (Teradata, Oracle, etc.), and should not be the first place you look. In my case, it took Tableau Support to examine the log files to see that temp tables were being built. The query was then restructured to eliminate the need for those temp tables and all was well.

                 

                As for BA S's comment about Tableau and Big Data, it's all about the database. If the database can supply Tableau with a bazillion rows of data, Tableau will handle it just fine. The problem is that the database server that holds those bazillion rows is choking and can't supply Tableau with the data it asked for. However, the user is using <BI tool of choice> as the UI, so naturally, it looks like the BI tool's fault. No BI tool has an error message that says "sorry, your puny database server can't handle your request so there's nothing we can do".

                 

                Some database types, namely flavors of Hadoop, are notoriously slow at querying, even with various query accelerators. You will simply never get the query performance that you would get from the likes of Oracle, SQL Server, Teradata, HANA, etc. Time outs for large query result sets are very common. That's why Tableau recommends creating an extract from Hadoop sources, vs. a live connection.

                 

                When diagnosing such symptoms, always start small and work up until it breaks.E.g., start by restricting the data source (often with a data source filter) that creates a small query data set, then create your viz. Note the query time(s). Gradually reduce the restriction on the filter until you are querying all the data (and getting the time-out error). Then, repeat these tests, but create an extract instead. The extract creation step will eventually hit a breaking point as well (because it's querying the same database), but you will find very good viz query performance with the previously successful extracts. Now, compare query times of the smaller data sets with the smaller extracts. If Tableau was having issues querying large amounts of data, you would see poor performance with both live and extract queries. However, you will not - extracts are faster. This proves that it is the database that is running the query (and timing out), not Tableau. I.e., how can Tableau time out on 500,000 rows of live data, but run like a champ on 500,000 rows in an extract? Because it is the database that is succeeding or failing to run the query, not Tableau (or other BI tools, for that matter).

                 

                As for your Perf Recording workbook, you'll need to save it as .TWBX format. The one you attached has no data, as it was saved as .TWB format.

                1 of 1 people found this helpful
                • 5. Re: Tableau takes a long time to execute a query
                  philip.watkinson

                  Hi Amit. I am using Tableau Desktop/ Server 10.5 with hyper, and it has taken 16 hours to run the simplest of queries on its way to creating an extract. This is simply unacceptable. Tableau needs to do something about it soon or they are going to start losing their edge.