February 22, 2010 at 12:37 pm
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)
SELECT 1, 'E[20]:Kristian'
INSERT [dbo].[TVFBreakout] (IntCode, DelimitedString)
SELECT 2, ''
INSERT [dbo].[TVFBreakout] (IntCode, DelimitedString)
SELECT 3, '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
IF ISNULL(@UDDataData,'') != ''
BEGIN
DECLARE @TempString VARCHAR(MAX),
@TempElement NCHAR(2),
@Stop BIT
IF CHARINDEX(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
WHILE ISNULL(@UDDataData,'') != ''
BEGIN
IF CHARINDEX(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
SET UDField1 = @TempString
ELSE
IF @TempElement = '2'
UPDATE @RtnValue
SET UDField2 = @TempString
ELSE
IF @TempElement = '3'
UPDATE @RtnValue
SET UDField3 = @TempString
ELSE
IF @TempElement = '4'
UPDATE @RtnValue
SET UDField4 = @TempString
ELSE
IF @TempElement = '5'
UPDATE @RtnValue
SET UDField5 = @TempString
ELSE
IF @TempElement = '6'
UPDATE @RtnValue
SET UDField6 = @TempString
ELSE
IF @TempElement = '7'
UPDATE @RtnValue
SET UDField7 = @TempString
ELSE
IF @TempElement = '8'
UPDATE @RtnValue
SET UDField8 = @TempString
ELSE
IF @TempElement = '9'
UPDATE @RtnValue
SET UDField9 = @TempString
ELSE
IF @TempElement = '10'
UPDATE @RtnValue
SET UDField10 = @TempString
ELSE
IF @TempElement = '11'
UPDATE @RtnValue
SET UDField11 = @TempString
ELSE
IF @TempElement = '12'
UPDATE @RtnValue
SET UDField12 = @TempString
ELSE
IF @TempElement = '13'
UPDATE @RtnValue
SET UDField13 = @TempString
ELSE
IF @TempElement = '14'
UPDATE @RtnValue
SET UDField14 = @TempString
ELSE
IF @TempElement = '15'
UPDATE @RtnValue
SET UDField15 = @TempString
ELSE
IF @TempElement = '16'
UPDATE @RtnValue
SET UDField16 = @TempString
ELSE
IF @TempElement = '17'
UPDATE @RtnValue
SET UDField17 = @TempString
ELSE
IF @TempElement = '18'
UPDATE @RtnValue
SET UDField18 = @TempString
ELSE
IF @TempElement = '19'
UPDATE @RtnValue
SET UDField19 = @TempString
ELSE
IF @TempElement = '20'
UPDATE @RtnValue
SET UDField20 = @TempString
ELSE
IF @TempElement = '21'
UPDATE @RtnValue
SET UDField21 = @TempString
ELSE
IF @TempElement = '22'
UPDATE @RtnValue
SET UDField22 = @TempString
ELSE
IF @TempElement = '23'
UPDATE @RtnValue
SET UDField23 = @TempString
ELSE
IF @TempElement = '24'
UPDATE @RtnValue
SET UDField24 = @TempString
ELSE
IF @TempElement = '25'
UPDATE @RtnValue
SET UDField25 = @TempString
ELSE
IF @TempElement = '26'
UPDATE @RtnValue
SET UDField26 = @TempString
ELSE
IF @TempElement = '27'
UPDATE @RtnValue
SET UDField27 = @TempString
ELSE
IF @TempElement = '28'
UPDATE @RtnValue
SET UDField28 = @TempString
ELSE
IF @TempElement = '29'
UPDATE @RtnValue
SET UDField29 = @TempString
ELSE
IF @TempElement = '30'
UPDATE @RtnValue
SET UDField30 = @TempString
IF @Stop = 1
SELECT @UDDataData = ''
END
END
RETURN
END
GO
/*test table valued function
*/
SELECT IntCode,
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
FROM dbo.[TVFBreakout]
CROSS APPLY [dbo].[TVFBreakoutFunction](DelimitedString) data
February 22, 2010 at 1:38 pm
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...
February 22, 2010 at 1:55 pm
I would advise you to read Jeffs great article :
"The "Numbers" or "Tally" Table: What it is and how it replaces a loop."
http://qa.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
February 23, 2010 at 12:50 am
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.
February 23, 2010 at 8:13 am
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... 😉
February 23, 2010 at 8:19 am
Sorry for the confusion - when I referred to both I meant the CLR (1) and Tally table (2) approaches.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply