5 Replies Latest reply on Aug 14, 2017 5:26 PM by Manjot Singh

    Stored Procedures - In Custom Sql

    Ratan Roy

      Hi All,

       

      I have written the stored procedure and using it in tableau to generate the report, But the report is very slow. I am getting the performance issue. Any Idea why is it like this....?

       

      I want to ask one query, can we write the stored procedures in the Custom sql box, i tried but i am getting error....?

       

      Could you please help how to improve the performance when i am using Stored procedure, It seems whenever i am using live connection its getting very slow and i cant make it extracted data because i have to create dynamic report .....?

       

      Please help me on this.

       

      Regards,

      Ratan

        • 1. Re: Stored Procedures - In Custom Sql
          Robert Sutter

          Ratan -

           

          Please see my comments inline:

           

          I have written the stored procedure and using it in tableau to generate the report, But the report is very slow. I am getting the performance issue. Any Idea why is it like this....?

           

          RS - What is the performance of the SPROC if you pass params locally in SQL Server (assuming that's your data source).

           

           

          I want to ask one query, can we write the stored procedures in the Custom sql box, i tried but i am getting error....?

           

          RS - You can't writ the entire SPROC in the Custom SQL box.

           

          Could you please help how to improve the performance when i am using Stored procedure, It seems whenever i am using live connection its getting very slow and i cant make it extracted data because i have to create dynamic report .....?

           

          RS - What piece of your report is dynamic and doesn't allow you to extract? A lot of the reports we write are very dynamic.

          • 2. Re: Stored Procedures - In Custom Sql
            Ratan Roy

            Hi Robert,

             

            Thank you for helping me on this.

             

            i have not used any parameter for this report in stored procedure - Please Correct me if i am missing something?

             

            i have deployed my report on the web page and whenever i am making any changes on the front end of web page, this should replicate in the tableau report also. Its taking much time and sometimes i am getting the error "Session ended by user" also.

             

            Regards,

            Ratan

            • 3. Re: Stored Procedures - In Custom Sql
              greg.jones.8

              This is how you incorporate Stored Procedures in custom SQL: Stored Procedures In SQL Server Without all the Tableau Overhead

              • 4. Re: Stored Procedures - In Custom Sql
                Bora Beran

                You can try the following approach that involves using Initial SQL to write results of the stored procedure to a temp table (this table will be session scoped and SQL server allows all users to create these so dbadmin doesn't have to tweak anything for you) and then doing a select from the temp table.

                 

                Initial SQL

                CREATE TABLE #TestTable ([name] NVARCHAR(60), [ID] INT);

                INSERT INTO #TestTable EXEC MyStoredProcedure

                 

                Custom SQL

                SELECT * FROM #TestTable

                 

                You can also CTEs directly from Tableau this way as documented here.

                 

                Using Common Table Expressions | Tableau Software

                 

                Nice thing about this approach is that Initial SQL doesn't run every time you take an action in Tableau. It will run in the beginning and create the table and as you drag fields into your viz or add filters etc. Tableau will query the results that are already stored in the temp table.

                3 of 3 people found this helpful
                • 5. Re: Stored Procedures - In Custom Sql
                  Manjot Singh

                  Hello Bora

                   

                  I have set up a dashboard with live connection, and I have to use Stored Procedure as a data source (there is no other alternative due to performance considerations, not even UDF). The stored procedure takes 1-2 sec to execute on the SQL Server, and the workbook on the Tableau Desktop seems pretty fast as well. But when I publish the workbook on the server, it takes around 20 sec to load suddenly.

                   

                  That is when I came across your solution above, and it seemed like worked initially, but then after I refresh the dashboard, it seems like Tableau caches two copies of the data and displays them alternatively on refresh. The data is no longer coming directly from the source it seems.

                   

                  Is there a way to get the stored procedure working close to its performance in SQL Server?

                   

                  Thanks

                  1 of 1 people found this helpful