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.
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.
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.
Queries fired by tableau.docx 190.8 KB
hey Matthew ..thanks for taking the time out .. let me try the extract route as well
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,