This series of posts attempt to look at the pros and cons of using Stored Procedures as data access method for Tableau reports. The points made here are valid as of Tableau 8.2.
Part 1 gives a general introduction and identifies areas where some of SProcs advantages become irrelevant when it comes to Tableau
Part 2 looks at metadata considerations
Part 3 looks at Execution Plan considerations
Part 4 looks at how Tableau uses stored procedures to answer different queries
Since applications such as Tableau attempt to read the metadata of a given query before actually executing the query, one should keep in mind the metadata considerations when talking about Additional Pre/Post Processing or breaking into simpler t-sql statements.
Simpler statements are better for SQL Server ‘s query optimizer compared to big monolithic sql statements. A complicated query would typically take longer time to compile a plan. And sometimes plan generation could terminate early and you could end up with a worse plan than possible. Instead, if the query is split into simpler statements (through use of temp tables or table variables etc.), the optimizer can arrive at execution plans in a faster amount of time and can generate more accurate query plans.
When given the choice between temp tables and table variables, the advantage (in most cases) lies on the side of temp tables. (As an aside, this stackoverflow post by Martin Smith contrasting temp tables and table variables is a must for every database developer). Also, when additional processing or logic in involved in stored procedure, it is very common to make use of temp tables to hold intermediate results.
However, temp tables and metadata only queries don’t quite fit together in SQL Server world. SQL Server provides two ways to ask the metadata only of a given query or stored procedure. Either to use FMTONLY or sp_describe_first_result_set. FMTONLY has been deprecated in SQL Server 2012 and sp_describe_result_set is its replacement. When using the above to query a SQL Server database for metadata, the database engine attempts to answer the query without actually executing the complete query. That means, it skips executing most of the code in the query/sproc and relies on the SELECT statements present in the query/sproc and gives the metadata that exists as of then. That means, if a temp table is present in the code, since the code is not actually executed, a temp table is not actually created and thus is unavailable for the query engine to infer the metadata. In such cases, SQL Server answers back with an error message. If using FMTONLY, there is a workaround to the temp table issue. However, if using sp_describe_result_set, then no such workaround exists.
Since Tableau first queries the database for metadata only instead of executing the whole query completely, any code using temp tables would error out.
Also, keep in mind that the metadata information present in the output even if the complete query is executed. The TDS packet (which is how SQL Server communicates data and requests with a client application) contains both the COLMETADATA and ROW token streams if row data is returned back to the client; in other words metadata is still available to be inferred even if the query is executed completely. This means that in future, if Tableau doesnt specifically ask for metadata only in the first pass, but executes the whole query and infers metadata from that output, the sprocs can still make use of stored procedures. Alternatively, if Microsoft changes the behavior of sp_describe_first_result_set to take into account temp tables, then also can temp tables be used in stored procedure.
So until then, keep in mind this limitation with temp tables when writing stored procedures for Tableau.
The next post touches upon execution plan considerations.