Should also mention that desktop and database server performance are fine.
Hey Steven -
Tableau issues metadata queries against the data source to understand what it's looking at. These queries (or sprocs) are executed by the database, of course -- so if it takes a long time to get a response, it generally is the RDBMS just working harder because it's dealing with a particularly complex schema. For example, I've seen Metadata exploration take an "extra" 30 seconds or longer against a big Oracle / Teradata schema... But never 30 minutes.
You say "using a statement that completes in 5 seconds when run via TSQL", so I want to be clear WHEN you're seeing this take so long in Tableau
- When you first connect to SQL Server and the database and we show you the list of tables?
- When you choose "Custom SQL"
- When you paste in your Custom SQL Statement?
- When you click "OK" after pasting in your statement?
- Or elsewhere?
It almost sounds like we're asking SQL for metadata and whatever sproc being executed by SQL is trying to access resources that are locked...so we sit there in a blocked state. You could probably see what's going on pretty easily with a SQL Profiler trace if you care to take a look...
If I can piggyback on this, I have the same problem with various datasource refreshes against a MySQL DB. For example, a pretty complex query takes 5 minutes to return when run in DB Visualizer. In Tableau, with the data source already defined and loaded in the workbook, once I select the datasource (defined as a live connection to MySQL), then go to Extract -> Refresh, it spends about 15-20 minutes "loading metadata", and then executes the query in the same amount of time (5 mins) as it does external to Tableau.
I saw the following post (http://community.tableau.com/thread/119749) suggesting that I create views to work around the problem. Haven't tried it yet, but am about to.
There are “Tableau Data Customizations” (TDC) that you can implement which tell the driver NOT do certain things that it would normally do if left alone.
The article above focuses on ODBC, but much of this stuff can (unofficially) be applied to native drivers, too. In the article above you’ll see a bunch of CAP_ODBC_METADATA_SUPPRESS_* entries which allow you to turn off certain metadata exploration features – if your database is slow in this regard, this might be a good approach.
But I’d try views first – way more simple
If you are still experiencing this problem would you please contact Support (firstname.lastname@example.org) so they may work with you to collect logs and explore this in more detail?
To tie in the other forum replies in this thread, you may find that you need to replace Custom SQL connections with the same query pasted into a database view to get the best performance.
After upgrading to desktop 8.1, I am no longer having this issue.
That's excellent news, thanks for reporting back!