4 Replies Latest reply on May 18, 2018 12:06 AM by Thomas Owen

    Getting data quickly from SQL

    Thomas Owen

      Hi,

       

      We connect to Azure SQL databases for all our SQL needs.

       

      The tables that we connect to contain a lot of information (circa 6 million records) in a fairly wide table and this is reasonably time consuming, regardless of the number of indexes we have on that table.  It is, quite simply, quite a lot of data - and there are a lot of joins that we need to do to get data from corresponding tables.

       

      We generally connect to the data using custom SQL Queries because there is a lot of underlying SQL functionality that we need to leverage.  No big deal, but this is where I have two questions:

       

      1.     Is it faster to retrieve data by writing a join directly within that custom SQL query, or is it faster to use the custom SQL query to get the main data and then perform any joins within the Tableau interface?  Or doesn't it really make any difference?

       

      2.     In every case where we connect to that table with 6 million records, we never need to actually retrieve all those records and so we filter them.  With this in mind, is it faster to retrieve filtered data by writing the filter directly into the custom SQL query, or is it faster to use the Tableau interface to construct the filter (using the Filters: Edit popup in the top-right of the Data Source page)?  Or doesn't it really make any difference?

       

      Thanks!

       

       

       

      Tom.

        • 1. Re: Getting data quickly from SQL
          Esther Aller

          Hey Thomas,

           

          When the joins are constructed in Tableau, then Tableau is able to do join culling in some situations, which can improve performance. Tableau will wrap custom SQL queries with additional SQL code, but it will never modify your custom SQL query. That being said, you can create and join several custom SQL queries in Tableau to get the best of both worlds.

           

          In my experience, it is faster to write the filter into a WHERE clause rather than use Tableau filters. I am joining several custom SQL queries tho, so this essentially allows me to filter the data before Tableau attempts to join it. If you are comparing filtering one custom SQL query in the WHERE clause versus adding a data source filter in Tableau then there might not be much difference. A data source filter means that Tableau is adding a WHERE clause outside of your custom SQL query.

           

          If you ever want to get really nitty-gritty with performance, you can see exactly what queries Tableau is sending to your database: Viewing Underlying SQL Queries | Tableau Software

           

          Hope this helps!

          • 2. Re: Getting data quickly from SQL
            Thomas Owen

            Hi Esther,

             

            Thanks for that - that's really helpful info and gives a very clear answer!

             

             

            Tom.

            • 3. Re: Getting data quickly from SQL
              Thomas Owen

              Incidentally, one thing I have learnt is that if you are working with extracts, as opposed to live data, and you put the filter on the custom SQL statement then the size of the extract will be affected.  If you get Tableau to do the filtering then it appears to extract all of the data and only apply the filter when you view the data - so the extract size is much larger.

              1 of 1 people found this helpful