THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

T-SQL Tuesday 24: Ode to Composable Code

TSQL2sDay150x150I love the T-SQL Tuesday tradition, started by Adam Machanic and hosted this month by Brad Shulz. I am a little pressed for time this month, so today’s post is a short ode to how I love saving time with Composable Code in SQL. Composability is one of the very best features of SQL, but sometimes gets picked on due to both real and imaginary performance worries. I like to pick composable solutions when I can, while keeping the perf issues in mind, because they are just so handy and eliminate so much hassle and complexity.

What is composability? Here’s a quick example. Stored procedure calls do not compose. That fact creates pretty ugly workarounds like creating temp tables, guessing at their schema requirements, and using insert … exec to get back results from a stored proc -- just to work with their results in any meaningful way:

SELECT col1, col2, col2
FROM dbo.mytable t
INNER JOIN dbo.myview v ON t.keycol = v.fkeycol
INNER JOIN EXEC dbo.storedProcThatJustReturnsRows p 
    ON oops.thatWill = NEVER.work –- #FAIL

This is because the results returned from a stored procedure are not really defined in terms of shape: the server cannot tell what to expect pack from a stored procedure, if anything, and therefore cannot allow that result to be composed in the context of an enclosing SQL Statement. That limitation is why I listed insert-exec among my Top 10 T-SQL Code Smells.

On the other hand, structures that do have a well-defined “result shape,” including views and table-valued functions, are composable:

SELECT col1, col2, col2
FROM dbo.mytable t
INNER JOIN dbo.myview v ON t.keycol = v.fkeycol
INNER JOIN dbo.myTVF( @someParameter ) ON ...

This provides a huge amount of flexibility when writing code.

The very best composable behavior is when the code also allows the SQL Server query optimizer to unpack and optimize the content of the composed structures in the larger context of your query, which is true in most cases for views and inline table-valued functions. There are some specific limitations that can prevent this type of expansion/optimization, and, of course, it’s important to be aware of those issues from a performance point of view. But in general, if you can allow the optimizer greater flexibility in choosing a query plan, it at least has a chance at improving performance.

Example: suppose I have a stored procedure that returns 10 columns and 100 rows, and I only really need the values from 2 columns in 25 rows. If I have to execute the proc, output all the results to a temp table, then work on them, there’s a decent chance the optimizer could factor out quite a lot of the work involved if it were given more options. Encapsulating the procedure code so fully as to force the optimizer into a specific execution sequence can actually limit the ways the query could be executed, and thereby “defeat” one of the server’s primary and most valuable features.

I don’t mean to argue that stored procs don’t have a place, because they do. I also would not argue that this type of inline optimization always works, because it doesn’t. But in the main I prefer to take advantage of composability when I can, only optimize back from that where there’s a legitimate reason to do so, and try in those cases to understand why optimization isn’t working.

(And three cheers to the SQL Server team for following this philosophy in moving to dynamic management views and functions and away from diagnostic stored procs!)

Published Monday, November 07, 2011 11:54 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

jamiet said:

Hi Merrill,

I wholly agree. On a gig earlier last year I advocated using multi-statement TVFs over sprocs for passing data into SSRS reports. Opponents argued (validly) that multi-statement TVFs are potentially dangerous (perf-wise) if you try and join them with other objects but I was prepared to use them if it meant that I could write a proper SELECT statement in my SSRS report rather than use EXEC. You have to impress upon people that they're not there for the purposes of JOINing, but I think its worth the risk.

Incidentally, Adam Machanic has a great blog post on this subject that I often refer people to:

Stored procedures are not parameterized views

(http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/stored-procedures-are-not-parameterized-views.aspx)

Regards

Jamie

November 8, 2011 3:55 AM
 

jamiet said:

With rather nice timing Rob Farley posted a blog post just hours before this one explaining the bad aspects of multi-statement TVFs: http://sqlblog.com/blogs/rob_farley/archive/2011/11/08/when-is-a-sql-function-not-a-function.aspx

November 8, 2011 6:09 AM
 

Rob Farley said:

I've just hit Publish on a similarly-themed post. I'll mention your post in the comments.

http://sqlblog.com/blogs/rob_farley/archive/2011/11/22/stored-procedures-with-ssrs-hmm-not-so-much.aspx

November 21, 2011 11:20 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement