3 Replies Latest reply on Sep 22, 2016 4:33 AM by Carl Slifer

    Poor Performance in MS SQL & Tableau Desktop

    Johnny Huang

      Bit of a strange thing going on here.  I have a Tableau Desktop 9.3.3 connecting to a MS SQL server 2014.

       

      I've setup the Data connection as a custom SQL which is "SELECT * FROM myVIEW WHERE TESTID=12345".  This query when executed as it in the DB runs in less than 1s.  Now, ideally I don't want to hard code in the "TESTID=12345" filter but for the same issue as I describe below, the query blows up in terms of time to return results.

       

      However, when I use this data source in Tableau exposing a column "ADDRESS" in the worksheet & via the Performance recording function, I see it actually throws "SELECT [Custom SQL Query].[ADDRESS] AS [ADDRESS] FROM (SELECT * FROM vETSIYoutubeKPIs where testid in (35338,35488)) [Custom SQL Query] GROUP BY [Custom SQL Query].[ADDRESS]".  This new query takes well over 2 minutes to run.

       

      Anyone able to help resolve this?

        • 1. Re: Poor Performance in MS SQL & Tableau Desktop
          Carl Slifer

          Hi Johnny,

           

          Is there a reason you're using Custom SQL as opposed to connecting to the table itself. It seems like here if you connected directly to the table/view vETSIYoutubeKPIS with tableau's SQL Connector you can apply a data source filter on the testid. This will then only bring into tableau the testid's you are looking for. Further the grouping bit is not needed in tableau unless you're aggregating something before bringing it in. In this case it does not seem you are. Tableau works fantastically with the underlying data without the need for pre-aggregation (in most circumstances). What's your goal for the group by here?

           

          1) Connect to the table/view directly

          2) Apply the datasource filter in question on testid

          3) If for some reason you only want address field (I'm assuming there is more) then you can select all of the extra fields and choose to hide them.

           

          Best Regards,

          Carl Slifer

          InterWorks

          3 of 3 people found this helpful
          • 2. Re: Poor Performance in MS SQL & Tableau Desktop
            Johnny Huang

            Hi Carl,

             

              Thanks for your reply.  Good questions! :-)

             

              I was using a Custom SQL because I was part way through my own testing / investigating on the poor performance.  To start from the beginning, here are the flow of events...

             

            1. I created a data source pointing directly at this view called 'vETSIYoutubeKPIS'.  I found performance issues i.e. doing anything would take ~2minutes to return data.

            2. I then inspected the view's SQL statement and saw there were a lot of complex joins etc so I suspected perhaps it was just a slow view to work.  So I proceeded to tune the SQL statement.

            3. Meanwhile, I traced the vendor application that was issuing SQL statements to achieve the same result & saw that it was simply executing "(@P1 bigint)Select * from vETSIYouTubeKPIs where TestId = @P1" where @P1 is just a variable parsed in, so I executed this ("SELECT * FROM vETSIYouTubeKPIs WHERE TESTID=12345") directly on the DB and it ran within 1s (wow!!)

            4. After seeing it run so quickly, I tried to mirror this in Tableau.  Using my data source created above (pointing directly at the view), in the worksheet I dragged TESTID to the filter card & put in 12345.  All good so far.  They I dragged a dimension called "E2EUserExp" in to the worksheet & it took 125s to execute.  Below the SQL Tableau issued to the database...

             

            SELECT [vETSIYouTubeKPIs].[E2EUserExp] AS [E2EUserExp]

            FROM [dbo].[vETSIYouTubeKPIs] [vETSIYouTubeKPIs]

            WHERE ([vETSIYouTubeKPIs].[TestId] = 12345)

            GROUP BY [vETSIYouTubeKPIs].[E2EUserExp]

             

            Obviously Tableau generates it's own SQL and is slightly (just!) more complex then the simple SELECT * before.  Now each time I run this it takes 125s to execute.  Interestingly, it always takes exactly 125s to execute no matter

             

            Upon further testing, it'd appear that that the simple SELECT * FROM vETSIYouTubeKPIs WHERE TESTID=12345 statement executed directly against the DB sometimes produces fast results but most of the time is actually very slow... so at this point, I'm suspecting more of a database tuning issue rather than Tableau related problems.

            • 3. Re: Poor Performance in MS SQL & Tableau Desktop
              Carl Slifer

              On site somewhere, can't reply in much detail, but I'd be curious if your

              server is indexed.

               

              On 22 September 2016 at 01:50, Johnny Huang <tableaucommunity@tableau.com>