Using Table Value Functions to Replace ‘Get’ Stored Procedures?

I learnt so many useful techniques, tricks and pointers at this SQLBits conference in York: tally tables, common table expressions (CTEs), cross applies, query tuning techniques, parallelism, data visualisation, to name but a few. However, the most useful technique I learnt was divulged in the first couple of hours during the training session given by Rob Farley.

In amongst the banter, jokes and excursions, a la Geordie comedian Ross Noble, was a complete gem which will make a huge difference to our existing and future projects.

Put simply, never use stored procedures for simple retrieves, but replace these with Inline Table-Value Functions (TVF).

Given our love of stored procedures, this is a difficult pill to swallow at first, but the benefit can be seen through the following example…

Suppose we have written a stored procedure that returns a dataset to the business layer of the application. In our case, we will have delivered the dataset via a WCF service.

Our stored procedure might be something like:
CREATE PROCEDURE dbo.usp_MyTable_Get (@FilterParms…)
AS
BEGIN
SELECT T.Column1, T.Column2, T.Column3, LT.Column2, …
FROM dbo.MyTable AS T
INNER JOIN dbo.MyLookupTable AS LT ON LT.IDCol = T.Column4
WHERE T.ColumnN = @FilterParm1

END

Now suppose that the application development team ask for an additional column to be included in the returned dataset. This may be because the dataset returns a foreign key identifier and the developers need to see the lookup description in the returned dataset. To return the lookup value we need to include an additional join on the lookup table.

Before going ahead, we’d confirm that the developer hasn’t or can’t get the lookup description from content held in memory; we’d explain that we’ll need to create a new stored procedure just for this situation, or, we’ll need to include it in the existing one which will (unnecessarily) adversely impact performance for instances where the service is used but the additional column is not required.

We have seen this situation many times and it is frustrating that an optimum one-size-fits-all solution hasn’t presented itself – until now…

The solution is to replace the stored procedure with an inline TVF which includes every possible join for foreign key lookups and every possible output column, and then build up SQL SELECTs on this TVF with the required columns relevant to each individual situation, letting the Query Optimizer exclude joined tables where necessary.
So, the above stored procedure is replaced with:

WCF Service
public List GetMyTable(dtoRequiredColumns, dtoParameters, dtoFilter)
{
List dtoOut = new List()
string strSQL = ""

foreach (col in dtoRequiredColumns)
{
if (col.IsRequired) strSQL += ", " + col.Name
}

strSQL = "SELECT" + strSQL.Substring(2) + " FROM dbo.tvfMyTableGet"

// add any WHERE and ORDER BY clause

SqlDataAdapter SqlDataAdapter = new SqlDataAdapter(strSQL, SQLConnection)
DataTable dtResult = new DataTable()
SqlDataAdapter.Fill(dtResult)

foreach (DataRow drRow in dtResult.Rows)
{
// add row to dtoOut
}

SqlDataAdapter.Dispose()

return dtoOut
}

and the TVF definition is something like the following:

CREATE FUNCTION dbo.tvfMyTable
RETURNS TABLE
RETURN
SELECT T.every column…,
LT1.everycolumn…,
LT2.everycolumn…,
LT3.everycolumn…,

FROM dbo.MyTable AS T
INNER JOIN dbo.MyLookupTable1 AS LT1
ON LT1.IDColumn = T.ForeignKeyID1
INNER JOIN dbo.MyLookupTable2 AS LT2
ON LT1.IDColumn = T.ForeignKeyID2
INNER JOIN dbo.MyLookupTable3 AS LT3
ON LT1.IDColumn = T.ForeignKeyID3

GO
So the function includes every column in MyTable, and is joined to every table that provides a lookup for foreign keys.

And here’s the clever thing, at run-time the optimizer looks at which columns are included in the SELECTed column list and excludes those JOINs on tables that are not required for that particular query. In addition, query plans are cached (however, note that different length string parameters will generate additional cached plans).

The objections might go something like – underlying data is made more secure by placing these behind a stored procedure ‘firewall’. Of course, just as with stored procedures, we can grant execute permission to the function and not the underlying tables or views. And, this looks like dynamic SQL, which is bad! But, we are not allowing any injection to take place here – the listed columns are defined within the dtoRequiredColumns object and full control of the ‘dynamic SQL’ is maintained.

Here at last is the one-size-fits-all solution, so thanks go to Rob Farley for this.

Read more from Rob Farley’s Blog here .

Advertisements

4 responses to “Using Table Value Functions to Replace ‘Get’ Stored Procedures?

  1. Still be VERY careful about SQL injection. I find it’s best to have static queries, but using Inline TVFs, so that the code is something like: strSQL = “select col1, col4 from dbo.someTVF(@param1, @param2) order by col4;”;

    But yes, take advantage of Inline TVFs to avoid the reuse of stored procs where the procs aren’t ideal.

    (but put brackets around your query: returns table as return (select…)

    Rob

  2. i know it’s just an example, but that looks prime for SQL Injection!

    Also, i’m fairly sure you’ll only get join elimination when using outer joins, not inner joins.

  3. Pingback: Tweets that mention Using Table Value Functions to Replace ‘Get’ Stored Procedures? | Xpert360 Ltd Development Blog -- Topsy.com

  4. Thanks for the feedback. Yes, SQL injection is certainly something to be careful of, however, column names will be literals defined in the WCF layer so this shouldn’t be an issue. No doubt we will have more to say on this once we have tried it out.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s