6 Replies Latest reply on Mar 6, 2013 11:57 AM by Robert Morton

    Custom SQL vs Data blending - if all things are equal which will yield better performance?

    STEVEN BAER

      We are developing a dashboard that is performing poorly that is using data blending. We have been told that writing custom sql will yield better performance but we don't see how that will help performance as we feel the issue , we are working off an extract, is how Tableau uses the data not how the data gets into the dashboard. Anybody out there have any experience with this type of situation. Unfortunately I doubt I will be able to publish the dashboard due the confidential nature of our data. Any input would be appreciated.

        • 1. Re: Custom SQL vs Data blending - if all things are equal which will yield better performance?
          Joshua Milligan

          Steven,

           

          Obviously, each situation will be different.

           

          If connecting live, custom SQL (as opposed to setting up the joins and letting Tableau understand the metadata and then blending) would probably not yield better performance as Tableau will execute the custom SQL every time it needs to get new data from the source.  The custom SQL will be wrapped in whatever SQL Tableau needs to aggregate the data to use to generate your view.  Of course, Tableau will optimize this process as much as possible, using cache, temp tables, etc...  Since you are using an extract, I don't think custom SQL is going to improve or degrade your performance.  This is because the custom SQL will only be run once to generate the extract.  After that, Tableau will generate and execute its own queries against the extract as determined by your view.

           

          Data blending can be more or less performant than joining the tables (using custom SQL or not) -- it just depends on the data source, the requested data, how the database is optimized, etc...  The thing to understand about blending is that it will be done in two queries (or more depending on how many sources you are blending).  The primary data source will have a query that will return all the records required at whatever aggregation is needed for your view.  The second query will be formed using whatever linking fields you have defined (filtered to the values for those fields present in the data returned by the primary query).  The second query will be done at the highest level of aggregation possible based on the linking fields.  So, blending can be more efficient, if the queries are aggregated at a high level and the database is property indexed/optimized.  But it can be less efficient if a single query could have returned the same results using a not-very-costly join.

           

          I know it's not a specific answer, but I hope that helps in understanding the options conceptually.  I'd be happy to answer any more questions you might have!

           

          Regards,

          Joshua

          1 of 1 people found this helpful
          • 2. Re: Custom SQL vs Data blending - if all things are equal which will yield better performance?
            Robert Morton

            Hi Steven,

             

            We have made a number of improvements to performance and functionality for Data Blending in the upcoming release of Tableau 8.0. If you continue to experience performance problems, we would love to learn more about your challenges. Please consider contacting our Support team (support@tableausoftware.com) so they may work with you to collect more information.

             

            Joshua gives an excellent response, and you may notice from other discussions of Data Blending on these forums that there are a wide variety of use cases, success stories and challenges with performance and functionality. Each situation may be very different, so you may need to explain your own challenges using example data that you can share freely that demonstrates the problem you are facing. Otherwise, I again suggest that you contact Support to discuss any specifics you can of your confidential data.

             

            Good luck,

            Robert

            1 of 1 people found this helpful
            • 3. Re: Custom SQL vs Data blending - if all things are equal which will yield better performance?
              STEVEN BAER

              Hi Joshua,

               

              I appreciate the response, it is always helpful to learn about how Tableau executes this type of operation.

               

              thank you

              Steve

              • 4. Re: Custom SQL vs Data blending - if all things are equal which will yield better performance?
                STEVEN BAER

                Hi Robert,

                 

                I just recently got access to some of the sessions from the Tableau User Conference and recall hearing that data blending will be better in 8 so that is nice. I would contact the support but time is of the essence and also in the past support always asks for a twbx and we cannot submit our data, so that is a bit of a catch 22 for us.

                 

                thank you

                Steve

                • 5. Re: Custom SQL vs Data blending - if all things are equal which will yield better performance?
                  Joshua Milligan

                  Robert,

                   

                  Is there any documentation or would you provide some detail as to what the specific differences are for blending in 8.0 from a technical perspective?

                   

                  Regards,

                  Joshua

                  • 6. Re: Custom SQL vs Data blending - if all things are equal which will yield better performance?
                    Robert Morton

                    Hi Joshua,

                     

                    There's no documentation yet on the technical details behind the Data Blending enhancements in Tableau 8.0. We recognize how useful it has been to our community to document such details in the past, so this is something we're interested in working on. Stay tuned...

                     

                    -Robert