11 Replies Latest reply on Jul 18, 2017 9:48 AM by Tom W

    Poor performance of Stored Proc

    Sourabh Chakraborty
    Sourabh Chakraborty Jul 4, 2017 12:58 AM

      Hi All,

       

      I have a requirement to connect multiple database based on user login and a stored proc with temp tables to be executed. The issue is that for every user with multi client access the stored proc is creating temp tables which is resulting in performance problem. I do not have option to create physical tables and views.

       

      Do we know of any tips and tricks to improve the performance of stored proc when called from tableau? It seems tableau is creating its own tables to dump the data from stored proc before executing the actual queries of the proc.

       

       

       

      • 811 Views
      • Tags:
        • 1. Re: Poor performance of Stored Proc
          Patrick A Van Der Hyde
          Patrick A Van Der Hyde Employee Jul 7, 2017 2:39 PM (in response to Sourabh Chakraborty)

          Hello Sourabh,

           

          Did you already review the Notes section of the help on this topic?  - it has several notes that may help here.

          Stored Procedures

           

          If this post assists in resolution, please mark it "Helpful".   If this post resolves your question, please mark this post as "Correct Answer"

          This will help other users with the same question locate this answer. 

           

          Thank you

           

          Patrick

          • 2. Re: Poor performance of Stored Proc
            Sourabh Chakraborty
            Sourabh Chakraborty Jul 12, 2017 3:29 AM (in response to Patrick A Van Der Hyde)

            Hi Patrick,

             

            We are using SQL server stored procedure as source for dashboard and the performance is really slow while the stored procedure is executing fast in DB side. What we understand that tableau creates its own temp table which is making this slow. Any suggestions.

             

            Some briefs about the project

             

            1. We have multiple databases and runtime we need to determine which database it should connect to. That’s why we are using SP as the SQL is dynamic in nature

             

            2. We tried initial sql functionality of tableau to load into a temp table , but it can only pass login tableau user. In our case one person can log in to multiple clients which means multiple database. We wanted to pass client id through initial sql but it seems a limitation

             

            3. We tried tableau custom sql but can’t change DB name dynamically

             

            Let me know if you can help with any of these options.

            • 3. Re: Poor performance of Stored Proc
              Tom W
              Tom W Ambassador Jul 12, 2017 5:40 AM (in response to Sourabh Chakraborty)

              I don't see why creating temporary tables would cause a performance problem - what data do you have to support this?

              I guess my point is, have you traced the execution of the stored procedure to see exactly what Tableau is doing versus what your stored procedure is doing? Can you attach the results of that here? Can you attach your stored procedure code here as well?

              • 4. Re: Poor performance of Stored Proc
                Sourabh Chakraborty
                Sourabh Chakraborty Jul 18, 2017 9:14 AM (in response to Tom W)

                Hi Tom,

                 

                Tableau is trying to open the data source connection 6 times in the workbook. Please find attached is the performance reading.  The reason we have to use stored proc is because the database name needs to change dynamically based on client ID the user logged in and some clients have around 100k records. The stored proc execution is quite decent in this case as it takes only 12 secs from DB but the report takes around 1.5 mins.

                 

                The same result would have been better in Tableau if it is a view as it takes only 20 secs but since the database name need to be changed dynamically so this is not a feasible option.

                 

                I have gone through several articles and community topics for this discussion which makes me believe that there is an overhead of temp table created when stored proc is called from tableau.

                https://community.tableau.com/docs/DOC-9021

                 

                Does the solution mentioned in the above article actually work? Is there a way to reduce the call to the data source? I am using Tableau 10.2 with MS SQL server 2014 with blending as the data is maintained at two grains.

                 

                Let me know your thoughts.

                • 5. Re: Poor performance of Stored Proc
                  Tom W
                  Tom W Ambassador Jul 13, 2017 6:17 AM (in response to Sourabh Chakraborty)

                  Can you please upload the workbook? Or at the very least, a screenshot so I can see how many connections you have in the workbook?

                  • 6. Re: Poor performance of Stored Proc
                    Sourabh Chakraborty
                    Sourabh Chakraborty Jul 18, 2017 4:28 AM (in response to Tom W)

                    Hi Tom,

                     

                    I have two connections and have used Data Blending since the data is at different levels of granularity.

                    • 7. Re: Poor performance of Stored Proc
                      Tom W
                      Tom W Ambassador Jul 18, 2017 6:15 AM (in response to Sourabh Chakraborty)

                      I can't help without seeing the workbook. If you don't want to provide one, please create a case with Tableau Support.

                      • 8. Re: Poor performance of Stored Proc
                        Sourabh Chakraborty
                        Sourabh Chakraborty Jul 18, 2017 9:13 AM (in response to Tom W)

                        Hi Tom,

                         

                        Please find the workbook attached. I need to use stored proc connecting to live database and will pass parameters. The stored proc uses dynamic sql with some temp tables creation. However the performance of SP is not bad at all - it takes 10 ~14 secs but the report takes 1.5 mins.

                         

                        Let me know if the dashboard needs some changes

                        • 9. Re: Poor performance of Stored Proc
                          Tom W
                          Tom W Ambassador Jul 18, 2017 9:11 AM (in response to Sourabh Chakraborty)

                          Using a live connection may be the cause here, but I'm guessing it's not possible for you to use an extract as you're using parameters.

                           

                          Tableau Support might be able to tell you more about why it's executing the stored procedure twice but my gut is this - it needs to execute once to get the metadata for the result set, then another time to get the data.

                          • 10. Re: Poor performance of Stored Proc
                            Sourabh Chakraborty
                            Sourabh Chakraborty Jul 18, 2017 9:16 AM (in response to Tom W)

                            Awesome reply Tom. But you should not need the twbx file to make this generic statement.

                             

                            Thanks nyways!!

                            • 11. Re: Poor performance of Stored Proc
                              Tom W
                              Tom W Ambassador Jul 18, 2017 9:48 AM (in response to Sourabh Chakraborty)

                              You might think that, but it's like asking me to make a diagnosis on a patient when all you're giving me is blood results, no medical history and no access to the patient. I'd suggest you take a read of this - Packaged workbooks: when, why, how

                               

                              I took a look at your workbook to see how you'd setup your connections (are they extracts or are they live?) because you hadn't provided that detail and to make sure you didn't have duplicates of the same connection which would result in the same stored procedure being fired multiple times because again, you had provided zero context for how many connections you had in the workbook.

                               

                              Next time I will be sure to link you to something like this https://community.tableau.com/search.jspa?q=stored+procedure+temp+table and wish you all the best.

                               

                              All the best!