3 Replies Latest reply on Jun 5, 2018 7:28 AM by Eddson Sierra

    How to optimise queries?

    Eric Saragosti

      Hi There,

      So, we're starting to deploy Tableau and we have developers both here and on the other side of the pond.

      We've run into some starvation issues.

      First part of the issue is that the query that Tableau creates is not optimal. Is there a way for me to edit that SQL, or must I create a view?

      Second, is it possible to set a timeout for Tableau? In order to prevent a single user from taking down a database?

       

      Thanks

        • 1. Re: How to optimise queries?
          Dmitry Chirkov

          What are you connecting to?

           

          Without examples of the queries it's quite difficult to suggest something but changing/tweaking sheets/dashboard will affect queries Tableau generates.

          Where is slowdown coming from? Heavy filters (introduced by filters and quickfilter controls)? Subqueries (introduced by LOD calculations, TOP N filters)?

           

          As for timeout - you can't set them on Tableau Desktop but there's configuration for that on Tableau Server which will work for both published dashboards and published datasources. I'd look here for that..

          • 2. Re: How to optimise queries?
            Eric Saragosti

            Hi Dmitry,

            In this case, it's an end user who's setting up data connections that are crashing the DB. I took a look at the query and noticed a few things that I'd like to optimize.

             

            The user does a whole lot of joins, and at the very end of the joins filters the results on if they have an active order. Were I to do this query in the DB, I'd have pulled a list of the active users and filtered the joins on that data using a with statement in order to pull a lot less data.

             

            At first glance, that's the first thing that I think really needs to be optimized. I suspect that the only way of doing this is through data blending but wanted to check.

            • 3. Re: How to optimise queries?
              Eddson Sierra

              Hi Eric,

               

              Not sure if this is still useful for you but there it is:

               

              It is possible to edit the query.

              1. Select Data> Convert to Custom SQL
              2. A window will open and there you can enter the code for your custom SQL.

               

              Source: Editing Custom SQL | Tableau Software

               

              Hope it helps!