5 Replies Latest reply on Jan 2, 2014 10:32 AM by Robert Morton

    Custom SQL not using cache ?

    vikram murthy

      Hi .. i am using a custom SQL for one of my views (which takes about 14-15 seconds to execute). I also have a couple filters linked to the parameter. To elaborate, the parameter has the division number which is passed to the SQL ..the SQL returns all data related to the division number (including departments and sub departments) ..the filters i have on the view are dept_num and subdept_num. The issue i am facing is that whenever i fiddle with the 2 filters mentioned above it runs the same SQL again hence tripling the time to render the report. I was wondering why Tableau isn't caching the result set and changing the view based on the filters from the local result set. Why does it need to hit the DB with the same query but wrappers that are getting meta data about dept_num and subdept_num. ? (i used SQL profiler to find out that the same query is being fired whenever i change the filters). I hope i have been clear enough. Any pointers would be greatly appreciated.

       

      Regards,

      vikram

        • 1. Re: Custom SQL not using cache ?
          Matt Lutton

          Are you using an extract of your data or a live connection?  If you are not using an extract, doing so may improve your performance significantly.

          • 2. Re: Custom SQL not using cache ?
            Joshua Milligan

            Vikram,

             

            I've read your post a couple of times and although I think I kind of understand the situation, it would be very helpful to see your setup (a screenshot of the view, including all the fields on the various shelves), the custom SQL, and the resulting queries from SQL profiler (as an aside, you can also get the queries Tableau executes from the log files).

             

            Matthews suggestion of using an extract is often a good one, but sometimes is not possible when you need to parameterize the SQL.  That may or may not be your case.

             

            Regards,

            Joshua

            • 3. Re: Re: Custom SQL not using cache ?
              vikram murthy

              hey Joshua ..thanks for taking the time out .. PFA a screen shot and the queries being fired by tableau .. i feel the later 2 queries are unwarranted (they are fired to get meta data about the filters dept and subdept) given that tableau already has the information about dept_num and subdept_num but i have no clue how to tell tableau that. Kindly let me know your thoughts.

              • 4. Re: Re: Custom SQL not using cache ?
                vikram murthy

                hey Matthew ..thanks for taking the time out .. let me try the extract route as well

                 

                Regards,

                vikram

                • 5. Re: Custom SQL not using cache ?
                  Robert Morton

                  Hi Vikram,

                   

                  Using Parameters in Custom SQL is a powerful way to express a complex data connection, but it may be overkill for your needs. Can you instead create a direct connection to the table(s) of interest, and use normal filters in Tableau on the data source fields or calculated fields that you create? If your Custom SQL is too complex, consider creating a VIEW in the database representing the same SELECT query, and then use Tableau to connect to that VIEW as it appears in the list of single-table connection options.

                   

                  The reason Parameters with Custom SQL can be slow is twofold. First, Tableau must be able to build its own queries without colliding with the existing query clauses used in the Custom SQL connection, so the Custom SQL itself is always placed inside a subquery; this can be bad for performance in many database systems which have a poor query optimizer or otherwise struggle with subqueries, including MySQL, Oracle and occasionally SQL Server. Second, when you have a Parameter inside of your Custom SQL connection, the current parameter value represents a single concrete connection in Tableau; altering such a parameter will cause Tableau to have to establish a new connection with the different parameter value, and this new connection will not be allowed to share a query cache with the prior connection, since the connections may be so fundamentally different that there's no safe way to reason about what parts of the query cache can be reused.

                   

                  So to summarize, both Custom SQL and Parameters separately represent powerful features that can lead to performance challenges, especially when used together. Consider whether or not you need this expressive power or can instead craft a different type of connection that uses simpler options for connecting (e.g. single-table) and simpler types of filtering (e.g. normal filters instead of parameter controls).

                   

                  I hope this helps,

                  Robert