Trying to max performance in this TVF

Question

Trying to max performance in this TVF

Trevster

SSC-Addicted

Points: 470
More actions
February 22, 2010 at 12:37 pm

#217100

Hi all,
I am looking for some assistance in imroving the performance of a table valued function I have written. The function itslef is a bit ugly but has been written for performance.
I am taking data (from a 3rd party database) which has up to 30 strings delimited (format: <E[ColumnIndex]:><data><CHAR(8)>) into a single column.
I wrote the function below so that it parses the column and populates each of the (up to) 30 fields into the correct column.
I am always looking at new approaches to writing T-SQL, so please provide as much constructive criticism as you see fit...
The script below should create a test table, put some data in it, create the function and use it in a CROSS APPLY.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*prepare table
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TVFBreakout]') AND type in (N'U'))
DROP TABLE [dbo].[TVFBreakout]
GO
CREATE TABLE [dbo].[TVFBreakout] (IntCode INT, DelimitedString VARCHAR(MAX))
GO
/*populate table
*/
INSERT[dbo].[TVFBreakout] (IntCode, DelimitedString)
SELECT1, 'E[20]:Kristian'
INSERT[dbo].[TVFBreakout] (IntCode, DelimitedString)
SELECT2, ''
INSERT[dbo].[TVFBreakout] (IntCode, DelimitedString)
SELECT3, 'E[1]:21/01/2010E[2]:21/02/2010E[3]:23.23E[4]:BE[5]:105020.00E[6]:yesE[8]:some notes
separated by a carriage returnE[9]:meE[10]:youE[11]:no'
INSERT[dbo].[TVFBreakout] (IntCode, DelimitedString)
SELECT 4, 'E[18]:Dupreys: Dupreys House.E[20]:our refE[22]:receivedE[23]:yes'
GO
/*drop (if exists) and create table valued function
*/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TVFBreakoutFunction]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[TVFBreakoutFunction]
GO
CREATE FUNCTION [dbo].[TVFBreakoutFunction]
(
@UDDataData VARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
UDField1 VARCHAR(MAX),
UDField2 VARCHAR(MAX),
UDField3 VARCHAR(MAX),
UDField4 VARCHAR(MAX),
UDField5 VARCHAR(MAX),
UDField6 VARCHAR(MAX),
UDField7 VARCHAR(MAX),
UDField8 VARCHAR(MAX),
UDField9 VARCHAR(MAX),
UDField10 VARCHAR(MAX),
UDField11 VARCHAR(MAX),
UDField12 VARCHAR(MAX),
UDField13 VARCHAR(MAX),
UDField14 VARCHAR(MAX),
UDField15 VARCHAR(MAX),
UDField16 VARCHAR(MAX),
UDField17 VARCHAR(MAX),
UDField18 VARCHAR(MAX),
UDField19 VARCHAR(MAX),
UDField20 VARCHAR(MAX),
UDField21 VARCHAR(MAX),
UDField22 VARCHAR(MAX),
UDField23 VARCHAR(MAX),
UDField24 VARCHAR(MAX),
UDField25 VARCHAR(MAX),
UDField26 VARCHAR(MAX),
UDField27 VARCHAR(MAX),
UDField28 VARCHAR(MAX),
UDField29 VARCHAR(MAX),
UDField30 VARCHAR(MAX)
)
AS
BEGIN
IFISNULL(@UDDataData,'') != ''
BEGIN
DECLARE @TempString VARCHAR(MAX),
@TempElement NCHAR(2),
@Stop BIT
IFCHARINDEX(CHAR(8),@UDDataData) = 0
SELECT@TempString = @UDDataData,
@UDDataData = ''
ELSE
SELECT@TempString = SUBSTRING(@UDDataData,1,CHARINDEX(CHAR(8),@UDDataData) - 1),
@UDDataData = SUBSTRING(@UDDataData,CHARINDEX(CHAR(8),@UDDataData) + 1, LEN(@UDDataData))
SELECT@TempElement = SUBSTRING(@TempString,3,CHARINDEX(']',@TempString) - 3),
@TempString = SUBSTRING(@TempString,CHARINDEX(':',@TempString) + 1,LEN(@TempString))
IF@TempElement != ''
IF@TempElement = '1'
INSERT@RtnValue(UDField1)
SELECT@TempString
ELSE
IF@TempElement = '2'
INSERT@RtnValue(UDField2)
SELECT@TempString
ELSE
IF@TempElement = '3'
INSERT@RtnValue(UDField3)
SELECT@TempString
ELSE
IF@TempElement = '4'
INSERT@RtnValue(UDField4)
SELECT@TempString
ELSE
IF@TempElement = '5'
INSERT@RtnValue(UDField5)
SELECT@TempString
ELSE
IF@TempElement = '6'
INSERT@RtnValue(UDField6)
SELECT@TempString
ELSE
IF@TempElement = '7'
INSERT@RtnValue(UDField7)
SELECT@TempString
ELSE
IF@TempElement = '8'
INSERT@RtnValue(UDField8)
SELECT@TempString
ELSE
IF@TempElement = '9'
INSERT@RtnValue(UDField9)
SELECT@TempString
ELSE
IF@TempElement = '10'
INSERT@RtnValue(UDField10)
SELECT@TempString
ELSE
IF@TempElement = '11'
INSERT@RtnValue(UDField11)
SELECT@TempString
ELSE
IF@TempElement = '12'
INSERT@RtnValue(UDField12)
SELECT@TempString
ELSE
IF@TempElement = '13'
INSERT@RtnValue(UDField13)
SELECT@TempString
ELSE
IF@TempElement = '14'
INSERT@RtnValue(UDField14)
SELECT@TempString
ELSE
IF@TempElement = '15'
INSERT@RtnValue(UDField15)
SELECT@TempString
ELSE
IF@TempElement = '16'
INSERT@RtnValue(UDField16)
SELECT@TempString
ELSE
IF@TempElement = '17'
INSERT@RtnValue(UDField17)
SELECT@TempString
ELSE
IF@TempElement = '18'
INSERT@RtnValue(UDField18)
SELECT@TempString
ELSE
IF@TempElement = '19'
INSERT@RtnValue(UDField19)
SELECT@TempString
ELSE
IF@TempElement = '20'
INSERT@RtnValue(UDField20)
SELECT@TempString
ELSE
IF@TempElement = '21'
INSERT@RtnValue(UDField21)
SELECT@TempString
ELSE
IF@TempElement = '22'
INSERT@RtnValue(UDField22)
SELECT@TempString
ELSE
IF@TempElement = '23'
INSERT@RtnValue(UDField23)
SELECT@TempString
ELSE
IF@TempElement = '24'
INSERT@RtnValue(UDField24)
SELECT@TempString
ELSE
IF@TempElement = '25'
INSERT@RtnValue(UDField25)
SELECT@TempString
ELSE
IF@TempElement = '26'
INSERT@RtnValue(UDField26)
SELECT@TempString
ELSE
IF@TempElement = '27'
INSERT@RtnValue(UDField27)
SELECT@TempString
ELSE
IF@TempElement = '28'
INSERT@RtnValue(UDField28)
SELECT@TempString
ELSE
IF@TempElement = '29'
INSERT@RtnValue(UDField29)
SELECT@TempString
ELSE
IF@TempElement = '30'
INSERT@RtnValue(UDField30)
SELECT@TempString
WHILEISNULL(@UDDataData,'') != ''
BEGIN
IFCHARINDEX(CHAR(8),@UDDataData) = 0
BEGIN
SELECT @Stop = 1
SELECT@TempString = @UDDataData
END
ELSE
SELECT@TempString = SUBSTRING(@UDDataData,1,CHARINDEX(CHAR(8),@UDDataData) - 1),
@UDDataData = SUBSTRING(@UDDataData,CHARINDEX(CHAR(8),@UDDataData) + 1, LEN(@UDDataData))
SELECT@TempElement = SUBSTRING(@TempString,3,CHARINDEX(']',@TempString) - 3),
@TempString = SUBSTRING(@TempString,CHARINDEX(':',@TempString) + 1,LEN(@TempString))
IF@TempElement != ''
IF@TempElement = '1'
UPDATE@RtnValue
SETUDField1 = @TempString
ELSE
IF@TempElement = '2'
UPDATE@RtnValue
SETUDField2 = @TempString
ELSE
IF@TempElement = '3'
UPDATE@RtnValue
SETUDField3 = @TempString
ELSE
IF@TempElement = '4'
UPDATE@RtnValue
SETUDField4 = @TempString
ELSE
IF@TempElement = '5'
UPDATE@RtnValue
SETUDField5 = @TempString
ELSE
IF@TempElement = '6'
UPDATE@RtnValue
SETUDField6 = @TempString
ELSE
IF@TempElement = '7'
UPDATE@RtnValue
SETUDField7 = @TempString
ELSE
IF@TempElement = '8'
UPDATE@RtnValue
SETUDField8 = @TempString
ELSE
IF@TempElement = '9'
UPDATE@RtnValue
SETUDField9 = @TempString
ELSE
IF@TempElement = '10'
UPDATE@RtnValue
SETUDField10 = @TempString
ELSE
IF@TempElement = '11'
UPDATE@RtnValue
SETUDField11 = @TempString
ELSE
IF@TempElement = '12'
UPDATE@RtnValue
SETUDField12 = @TempString
ELSE
IF@TempElement = '13'
UPDATE@RtnValue
SETUDField13 = @TempString
ELSE
IF@TempElement = '14'
UPDATE@RtnValue
SETUDField14 = @TempString
ELSE
IF@TempElement = '15'
UPDATE@RtnValue
SETUDField15 = @TempString
ELSE
IF@TempElement = '16'
UPDATE@RtnValue
SETUDField16 = @TempString
ELSE
IF@TempElement = '17'
UPDATE@RtnValue
SETUDField17 = @TempString
ELSE
IF@TempElement = '18'
UPDATE@RtnValue
SETUDField18 = @TempString
ELSE
IF@TempElement = '19'
UPDATE@RtnValue
SETUDField19 = @TempString
ELSE
IF@TempElement = '20'
UPDATE@RtnValue
SETUDField20 = @TempString
ELSE
IF@TempElement = '21'
UPDATE@RtnValue
SETUDField21 = @TempString
ELSE
IF@TempElement = '22'
UPDATE@RtnValue
SETUDField22 = @TempString
ELSE
IF@TempElement = '23'
UPDATE@RtnValue
SETUDField23 = @TempString
ELSE
IF@TempElement = '24'
UPDATE@RtnValue
SETUDField24 = @TempString
ELSE
IF@TempElement = '25'
UPDATE@RtnValue
SETUDField25 = @TempString
ELSE
IF@TempElement = '26'
UPDATE@RtnValue
SETUDField26 = @TempString
ELSE
IF@TempElement = '27'
UPDATE@RtnValue
SETUDField27 = @TempString
ELSE
IF@TempElement = '28'
UPDATE@RtnValue
SETUDField28 = @TempString
ELSE
IF@TempElement = '29'
UPDATE@RtnValue
SETUDField29 = @TempString
ELSE
IF@TempElement = '30'
UPDATE@RtnValue
SETUDField30 = @TempString
IF@Stop = 1
SELECT @UDDataData = ''
END
END
RETURN
END
GO
/*test table valued function
*/
SELECTIntCode,
data.UDField1,
data.UDField2,
data.UDField3,
data.UDField4,
data.UDField5,
data.UDField6,
data.UDField7,
data.UDField8,
data.UDField9,
data.UDField10,
data.UDField11,
data.UDField12,
data.UDField13,
data.UDField14,
data.UDField15,
data.UDField16,
data.UDField17,
data.UDField18,
data.UDField19,
data.UDField20,
data.UDField21,
data.UDField22,
data.UDField23,
data.UDField24,
data.UDField25,
data.UDField26,
data.UDField27,
data.UDField28,
data.UDField29,
data.UDField30
FROMdbo.[TVFBreakout]
CROSS APPLY[dbo].[TVFBreakoutFunction](DelimitedString) data

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You should look into using a split string function based on a Tally (or Numbers) table.

The related article in my signature describe the concept. You could also search for "split string function" on this site to find various solutions.

If you're already using CLR functions you probalby want to specifically search for a CLR solution since it seems to perform best in most cases compared to the plain T-SQL solutions.

I would use the split function with '[' as the first delimiter to separate rows based on the beginnig of the column index and the CHARINDEX function to separate ColumnIndex and data.

This concept should perform a lot better compared to what you have so far...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Johan Bijnens SSC Guru Points: 135551 More actions · Answer 2

I would advise you to read Jeffs great article :

"The "Numbers" or "Tally" Table: What it is and how it replaces a loop."

http://www.sqlservercentral.com/articles/T-SQL/62867/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Trevster SSC-Addicted Points: 470 More actions · Answer 3

Ah, thanks guys - I have actually already used a tally table in another part of the same solution, although it was a couple of years ago, clever stuff...

I'll give both a try and report back, thanks again.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Drammy (2/23/2010)
Ah, thanks guys - I have actually already used a tally table in another part of the same solution, although it was a couple of years ago, clever stuff...
I'll give both a try and report back, thanks again.

There aren't really two solutions... Both of us (ALZDBA and myself) referred to the same concept and even to the same article... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Trevster SSC-Addicted Points: 470 More actions · Answer 5

Sorry for the confusion - when I referred to both I meant the CLR (1) and Tally table (2) approaches.