Creating an automatic View to an In-line table function conversion stored procedure

Introducing the Problem

We have a series of views in our organization’s application database. The views definitions are all static queries that consist of few or many condition clauses.

When the application first started with the initial data, the views operated normally, but after a while, after a period where data volume increased, the application’s performance time, through the views usage, degraded.

One of the reasons for the degraded performance time is the output views large result sets, because these sets were not filtered properly. This fact was also not detected by QA when testing the application in the applications acceptance phase.

After a thorough examining of the source of the performance problems, management decides that a series of in line table functions that can be parameterized must be created so that the data from querying the views will be replaced by these functions and filtered at the source.

The reasoning behind this decision comes from the fact that In-line User Defined functions can be thought of as views that accept parameters and can be more efficient than simple views.

This flavor of User Defined function can be used effectively to return different data from the same tables based on the parameter values without losing the functionality of the view.

The in line user defined function return tables and can be treated as another row set and thus, you can join other tables within your code module to the output of a UDF. For example, if you have a UDF returning two best-selling books along with their writers, you can easily join it with other tables.

Another benefit of User Defined Inline function is that it the inline table value function is able to expose its execution plan to an external query, thus increasing throughput. Inline functions are also economic since it does not declare any local variables because its TSQL body consists of parameters and query and not any variable declarations.

The suggested solution

The suggested solution presented here involves creating a T-SQL stored procedure that will create the in line table function automatically, directly from the existing view definition taken from the SQL Meta data.

The function creation is done by using dynamic T-SQL. The function name will be like the view name only prefixed by ‘udf_’ and then the view name.

After the procedure execution, both the view and function will remain in the application database. It is up to the programmer to link the code to the new defined function or to keep the existing code that uses the view.

Here is the suggested stored procedure T-SQL code:

CREATE PROCEDURE usp_convert_view_to_inline_TF (

@schemaName NVARCHAR(30) = 'dbo'

,@viewName NVARCHAR(30)

,@filterparam NVARCHAR(40)

,@paramtype NVARCHAR(20)

,@compare Token NVARCHAR(40) )

BEGIN

DECLARE @udf_def NVARCHAR(max)

DECLARE @view_def NVARCHAR(max)

DECLARE @udf_name NVARCHAR(100)

DECLARE @query_start INT

SET @udf_name = @schemaName + '.' + 'udf_' + @viewName + ' (' + @filterparam + ' ' + @paramtype + ') '

IF EXISTS (SELECT 1

FROM SYS.OBJECTS a

WHERE object_name(a.object_id) = @viewName

AND schema_name(a.schema_id) = @schemaName

AND a.type = 'V' )

BEGIN

SET @udf_def = 'CREATE FUNCTION ' + @udf_name + 'RETURNS TABLE AS RETURN '

SELECT @view_def = DEFINITION

FROM sys.sql_modules

WHERE [object_id] = OBJECT_ID(@viewName);

SET @query_start = CharIndex('SELECT ', @view_def, 1)

SET @udf_def += SUBSTRING(@view_def, @query_start,

len(@view_def) - @query_start + 1) +

' AND ' + @compareToken + ' = ' + @filterparam

EXEC (@udf_def)

PRINT 'Function ' + @udf_name + ' Created.'

END

ELSE

BEGIN

PRINT 'Function ' + @udf_name + ' Not Created. View ' + @schemaName + '.' +

@viewName + ' Not Found.'

END

Here are some explanations for the code:

I called the procedure usp_convert_view_to_inline_TF, It should be compiled in the application’s database, along with the others view and functions.

The procedure gets the current view name and the views schema name (the default value defaults to dbo), it gets the new function filter parameter name , the parameter type and the expression inside the view’s definition that needs to be compared to the new parameter.

Please note that the procedure’s assumption is that there is only one filtering condition that will be added to the original view’s definition. This is assumed for code Simplicity, however , in many cases , the filtering done is much more complex and consists of several filtering conditions, from one to ten or even twenty filtering conditions.

In order to add more than one condition there are two main methods for accomplishing this task:

Adding additional procedure input parameters for each condition added in the following manner:

@filterparam1 nvarchar(40),
@paramtype1 nvarchar(20),
@comparetoken1 nvarchar(40),
@filterparam2 nvarchar(40),
@paramtype2 nvarchar(20),
@comparetoken2 nvarchar(40)
.
.
.

For each added condition, three more parameters: filter parameter, parameter type and compare to token.

You can limit it to N conditions. Also, you can add as many as you wish you wish. You can also default the input parameters to NULL.

In that case, when calling the procedure, just send the non-empty parameters but check fir null values of the input when constructing the TSQL that creates the function.
The second method is changing the filter parameter; parameter type and compare to token parameters to comma separated lists. In the procedures API change them to:

@filterparamList nvarchar(400),
@paramTypeList nvarchar(200),
@compareToTokenList nvarchar (400)

When constructing the dynamic SQL statement, add a loop that parses the comma separated strings and adds the conditions to the body of the query.

If you use the UDF name TSQL variable in order to construct the new function signature, please enlarge this variable to 1000 characters (instead of 100 as it is written in the code here).

The procedure checks the sys.objects system view for a view in that name and schema. If no such view exists, it reports an error and that the function will not be created and exits.

If the view exists, the procedure extracts its definition from the sys.sql_modules system view and constructs a dynamic T-SQL in line tables creation statement that includes the schema name, the function name that is actually the view name with ‘UDF_’ as a prefix and the views query extracted from the definition string.

The procedure then adds to the views condition list the filtering clause constructed by comparing the given parameter name to the compared expression from the view definition.

The procedure executes the constructed string dynamically and reports the creation of the function.

Example of testing the procedure

For our test, let’s take Microsoft’s Northwind sample database that can be downloaded from Microsoft site (it’s called sample databases package).

For example. Let’s create a view that joins categories and products giving category name, product name and product price

CREATE VIEW ProductsCatalog

SELECT categories.CategoryName

,products.ProductName

,products.UnitPrice

FROM categories

,products

WHERE categories.CategoryID = products.CategoryID

After creating the view, let’s execute our procedure, filtering on the category name:

EXEC usp_convert_view_to_inline_TF

@viewName = 'ProductsCatalog'

,@filterparam = '@catname'

,@paramtype = 'nvarchar(15)'

,@compareToken = 'categories.CategoryName'

The procedure operates on the ProductsCatalog View, extracts its definition , take the SELECT statement and uses it to create an in line table function creation statement that accepts the filtering parameter, the query from the view and the additional filtering clause from the @filterparam and @compareToken parameters.

Now, we get the following in line table function

CREATE FUNCTION dbo.udf_ProductsCatalog (@catname nvarchar(15))

RETURNS TABLE AS

RETURN

select categories.CategoryName, products.ProductName, products.UnitPrice

from categories, products

where categories.CategoryID = products.CategoryID AND

categories.CategoryName = @catname

Now we can execute it and filter on category name

For example:

SELECT *

FROM dbo.udf_ProductsCatalog('Beverages')

Note:

If we try to execute the procedure with a non-existing view like ProductsCatalogX (For example), we will get

Function dbo.udf_ProductsCatalogX (@catname nvarchar(15)) Not Created. View dbo.ProductsCatalogX Not Found.

Author
Recent Posts

Eli Leiba

Eli Leiba is a senior application DBA at Israel Electric Company, a teacher at Microsoft CTEC, and senior database consultant with 24 years of experience working with both SQL Server and Oracle RDBMS. He is certified in Oracle and SQL Server database administration and implementation and has a B.S. in Computer Science.He can be reached at: iecdba@hotmail.com

View all posts by Eli Leiba

Introducing the Problem

The suggested solution

Here is the suggested stored procedure T-SQL code:

Here are some explanations for the code:

Example of testing the procedure

Related posts: