2 Replies Latest reply on Jul 15, 2015 11:49 AM by Matt Coles

    Extract vs Database Performance / Desktop vs Web Delivery

    Tom Gilkison

      We are publishing reports to the Tableau Server, and using the JavaScript API to display the reports on a Web page, passing filters from the page to the reports.  We would like to enhance the performance, as it might take a minute for the report to load. 

       

      Issue 1:  It seems that Tableau is not able to use a dynamic custom query to pull the data from the database.  The custom query is used to get a result set of ALL data, and then the filter is applied on ALL data.  Is this correct?  Is there any way to pass the filter into the SQL WHERE clause?

       

      Issue 2:  The performance enhancement of the extract seems to be in the use of the Desktop application, as the data doesn't have to be pulled across the wire to process it.  I uploaded two of the same reports to the server.  One with extract, and the other using the direct SQL custom query.  Here are the results:

       

      Extract Page Load Time (seconds)         Database Page Load Time (seconds)

      8                                                            6

      8                                                            7

      5                                                            5

       

      The Custom SQL Query performs slightly faster than the extract.  It makes sense, because the server processes both the extract and the SQL locally (although the SQL server is near), renders the report and sends the HTML to the browser.  The TDE data is compressed, so It might take slightly longer to generate the result set.

       

      We are trying to determine if we need to use Extracts for performance, or if we should be looking to optimize the SQL.  Use of Extracts also comes at a cost of ensuring Scheduling is turned on, setting up the schedule, and assigning a schedule to a report - none of which I can find in TabAdmin.  Our Server Admin doesn't like manual process.  Am I correct that this has to be done manually?

        • 1. Re: Extract vs Database Performance / Desktop vs Web Delivery

          Hey Tom,

           

          I'm moving this to our Server Admin forum as I think some of our admins could have some valuable input on this.

           

          Perhaps Jeff Strauss

          • 2. Re: Extract vs Database Performance / Desktop vs Web Delivery
            Matt Coles

            If you are using Custom SQL, that query will always be run in its entirety for any info Tableau needs from the data connection--not very efficient, and using it can be a big performance drain. You can pass criteria into the Custom SQL, but only via a Parameter value. And a Parameter value cannot be changed dynamically, except I believe you can set the value with the Javascript API. So that actually might be a solution for you in your case.

             

            I have rarely seen extracts perform more poorly than a live database, but if you're running a columnar analytics-oriented database, I could see it being better. In general, we've found that making improvements to the workbook itself is generally of the most help in improving performance, before trying to do database tuning and such. There are a whole host of tips and tricks out there for doing this.