Rewrit a Scalar function to a table valued function

Ambertje

Hi everyone, I have a view with multiple outer joins and a on a lot of the fields I'm invoking a scalard functions. The result is a very poor performance. I've been browsing the internet quite some time now (also read the article: Using Table-Valued Functions in SQL Server) but I can't seem to manage it on my own. I know the difference between SVF and TVF and that's why I want to use TVF. This is the Scalard function I want to recreate into a Table Valued Function: The Shipping unit of my articles must be divided by 100 except for articles < 99999 or article 4859696

USE [Staging]
GO

/****** Object: UserDefinedFunction [dbo].[Devide_Shipping_Unit] Script Date: 21/10/2014 11:46:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[Devide_Shipping_Unit]

(
@ValueString nvarchar(8),
@ArticleString nvarchar(8)
)
RETURNS Int

AS
BEGIN

DECLARE @result int

IF ISNUMERIC(@ValueString)<> 1 OR ISNUMERIC(@ArticleString)<> 1
	BEGIN 
		set @result  = 0
	END 
ELSE
	BEGIN 
					
		if  Cast(@ArticleString AS int) <= 99999  or @ArticleString = '4859696' 
			set @result  = 1 --CAST(@ValueString AS  int) / 1000
		Else 
			set @result  =CAST(@ValueString AS  int) / 100
			
	END 
return @result

END

GO

Another scalar function is a conversion of dates to a numeric datatype:

ALTER FUNCTION [dbo].[CONVERT_DATE_TO_NUMERIC]
(
@dateValueString nvarchar(10)
)
RETURNS Numeric(8, 0)
AS
BEGIN

DECLARE @result Numeric(8, 0)

--DECLARE @dateValueString nvarchar(10)  = '03/12/2013'
DECLARE @startdate nvarchar(10)  = ''

-- set @dateValueString = '2013-07-23'
set @dateValueString = REPLACE(@dateValueString,'/','')
set @dateValueString = REPLACE(@dateValueString,'-','')

declare  @\_year nvarchar(4) =  LEFT(@dateValueString,4)
declare  @\_month nvarchar(2) =  SUBSTRING(@dateValueString,5,2)
declare  @\_day nvarchar(2) =  RIGHT(@dateValueString,2)


set @startdate = @\_year + @\_month + @\_day

IF ISNUMERIC(@startdate)<> 1
	BEGIN 
		set @result  = 0
	END 
ELSE
	BEGIN 
		set @result  = @startdate 		END 


return @result

END

GO

Can anybody please help me I'm stuck :sigh: Ambertje

Mycroft Holmes

Have you used SQL Profiler to identify the expensive parts of the query. It is much more likely you are missing indexes. Try that before dumping on the scalar functions. A scalar that does not reference another database object (eg looks up a table) is not going to have a great deal of impact.

Never underestimate the power of human stupidity RAH

Ambertje

I'm working with Microsoft SQL Server Management Studio, I've searched for the profiler but can't find it. Do you mean an SQL Server Profiler? Can you give me an example or a link to where I can find that please? This is what I get when I Set Statistics IO and Time ON:

Table 'Worktable'. Scan count 252, logical reads 20839, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AD'. Scan count 1, logical reads 109674, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'D_Article'. Scan count 1, logical reads 729, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'porcKD'. Scan count 1, logical reads 1473, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 91947 ms, elapsed time = 119500 ms.

I wanted to create indexes on the View but a View doesn't allow to create indexes. The view gets the data out of the bottom (STAGING) area in the form of text files. The View is used to load the data through SSIS in TOP area (DWH) No table in the Staging area contains indexes.

Richard Deeming

Try adding WITH SCHEMABINDING to your functions, between the RETURNS <type> and As lines. http://www.sqlservercentral.com/blogs/sqlstudies/2014/09/15/i-schemabound-my-scalar-udf-and-you-wont-believe-what-happened-next/[^] ISNUMERIC is not reliable. All of the following values will be considered numeric, but cannot be cast to an integer:

ISNUMERIC('-')
ISNUMERIC('.')
ISNUMERIC('-$.')

If you're using SQL 2012 or higher, use the new TRY_PARSE function[^] instead. Otherwise, test that the string doesn't contain any non-numeric characters:

If @ValueString Like '%[^0-9]%' Or @ArticleString Like '%[^0-9]%'
Begin
-- Either @ValueString or @ArticleString are not valid integers.
set @result = 0
End

Assuming you're starting with a date or datetime / datetime2 type, your CONVERT_DATE_TO_NUMERIC function can be replaced with:

Convert(int, Convert(char(8), YourDateColumn, 112))

Passing 112 to the Convert function[^] formats the date as yyyyMMdd. Converting that to an integer gives the same result as your function.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Richard Deeming

Ambertje wrote:

a View doesn't allow to create indexes

Yes it does[^]. You just need to make sure the view is created WITH SCHEMABINDING. However, to properly use an indexed view on any edition lower than SQL Enterprise, you'll need to use the WITH ( NOEXPAND ) query hint.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Ambertje

My view contains aggregates, outer joins, subqueries so With Schemabinding wont work

Lost User

I'm going to suggest something weird. It looks as if you are using the scalar function to convert a single column into another. That could also be done before your query is executed, and that "might" save time. Might, as I haven't tested it :) Add the required int-column for the date, and run an update-query to do the conversion. Your table is now one column larger, but lost a calculation. You could keep the converted date consistent by updating them over a trigger when a record is inserted/modified. If you're not allowed to modify the original table, you can always create a new table and add the primary key of the original. That way you can eliminate the need for the function completely, and move the calculation to the moment the records is inserted/updated. That delay may be hardly noticeable when manipulating a single record, while it adds up to a lot of calculations if it has to be done "on the fly".

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Mycroft Holmes

Ambertje wrote:

The view gets the data out of the bottom (STAGING) area in the form of text files.

Ah I thought you were doing something sensible, expecting performance from text files is not going to work. I suggest you bulk copy the text files into staging tables, do attempt to apply transformations, and then use a stored proc to do the transforms from staging to your destination. I would expect an order of magnitude performance gain with proper tuning of the staging indexes and procedure design. If the data is extreme you may want to look into dropping staging indexes before the insert and reimplementing them after import. Doing transforms on the way in is a really lousy design, I know SSIS and all the tools do this but it is just wrong. Extract, Load, Transform

Never underestimate the power of human stupidity RAH