December 29, 2017 at 9:26 am
Hi All,
I am using a Function code below for each row, this is called for 15000 rows. and it is working very slow.
Can any one please help me
Declare
@InputString VARCHAR(8000),
@Delimiter VARCHAR(50),
@ConsumptionFlags VARCHAR(8000),
@IgnoredConsumptionFlags VARCHAR(8000),
@persistConsumptionFlags VARCHAR(8000),
@persistxml XML
SET @InputString = '0|0|0|1|0|0|54|78|0|0|0|147|0|0|0|0|0|0|0'
SET @Delimiter = '|'
SET @ConsumptionFlags = '0001000000000000000'
SET @IgnoredConsumptionFlags = null
SET @persistConsumptionFlags = '7|7|7|7|7|7|1|1|7|7|7|1|7|7|7|7|1|1'
SET @persistxml = '<Item>7</Item><Item>7</Item><Item>7</Item><Item>7</Item><Item>7</Item><Item>7</Item><Item>1</Item><Item>1</Item><Item>7</Item><Item>7</Item><Item>7</Item><Item>1</Item><Item>7</Item><Item>7</Item><Item>7</Item><Item>7</Item><Item>1</Item><Item>1</Item>'
DECLARE @Result NVARCHAR(Max)
Declare @Items TABLE
(
[Id] [int] IDENTITY(1,1) NOT NULL,
Item VARCHAR(8000)
)
Declare @Persist TABLE
(
[Id] [int] IDENTITY(1,1) NOT NULL,
Item [int]
)
INSERT INTO @Persist
SELECT Item = T.c.value('.','varchar(20)')
FROM @persistxml.nodes('/Item') T(c)
WHERE T.c.value('.','varchar(20)') <> ''
IF @IgnoredConsumptionFlags = ''
SET @IgnoredConsumptionFlags = null
IF @Delimiter = ' '
BEGIN
SET @Delimiter = ','
SET @InputString = REPLACE(@InputString, ' ', @Delimiter)
END
IF (@Delimiter IS NULL OR @Delimiter = '')
SET @Delimiter = ','
DECLARE @Item VARCHAR(8000)
DECLARE @ItemList VARCHAR(8000)
DECLARE @DelimIndex INT
DECLARE @index INT
SET @ItemList = @InputString
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
SET @index = 1
WHILE (@DelimIndex != 0)
BEGIN
SET @Item = SUBSTRING(@ItemList, 0, @DelimIndex)
IF (Convert(INT,@Item) = 1 and 0 = (SUBSTRING(@ConsumptionFlags, @index, 1)))
BEGIN
SET @Item = Convert(INT,@Item) + 1
--PRINT Convert(varchar,@Index) + ' - ' + SUBSTRING(@ConsumptionFlags, @index, 1) + ' - ' + Convert(varchar, Convert(INT,@Item) + 1)
END
INSERT INTO @Items VALUES (@Item)
SET @ItemList = SUBSTRING(@ItemList, @DelimIndex+1, LEN(@ItemList)-@DelimIndex)
SET @DelimIndex = CHARINDEX(@Delimiter, @ItemList, 0)
SET @index = @index+ 1
END
IF @Item IS NOT NULL
BEGIN
SET @Item = @ItemList
IF (Convert(INT,@Item) = 1 and 0 = (SUBSTRING(@ConsumptionFlags, @index, 1)))
BEGIN
SET @Item = Convert(INT,@Item) + 1
END
INSERT INTO @Items VALUES (@Item)
END
ELSE INSERT INTO @Items VALUES (@InputString)
-- Delete Condition which we do not want to Show / Select -- Block Start
DELETE @Items WHERE ID IN ( 9,10,12,17)
IF ( ( Select Count(*) FROM @Items WHERE ID in (18,19)) = 2)
BEGIN
IF EXISTS(
Select *
FROM @Items I
JOIN @Persist SI ON SI.ID = I.ID
WHERE I.Item <> 0 AND Convert(INT,I.Item) <= Convert(INT,SI.Item)
AND I.ID = 18
)
BEGIN
DELETE @Items WHERE ID = 19
END
END
IF ( ( Select Count(*) FROM @Items WHERE ID = 1 and Item > 0) = 1)
BEGIN
IF EXISTS(
Select *
FROM @Items I
JOIN @Persist SI ON SI.ID = I.ID
WHERE I.Item <> 0 AND Convert(INT,I.Item) <= Convert(INT,SI.Item)
AND I.ID = 1
)
BEGIN
DELETE @Items WHERE ID = 11
END
END
-- Block End
DELETE @Items WHERE ID IN (17,18,19) and Item > 1
SELECT @Result = COALESCE(@Result + '|', '') + Convert(varchar,I.Id) + ',' + Convert(varchar,I.Item)
FROM @Items I
JOIN @Persist SI ON SI.ID = I.ID
WHERE I.Item <> 0 AND Convert(INT,I.Item) <= Convert(INT,SI.Item)
AND I.ID NOT IN
(
select ID
from [SplitFunction_Condition](@IgnoredConsumptionFlags,'') WHERE Item <> 0
)
Select @Result
December 29, 2017 at 9:30 am
If that's in a scalar user-defined function, it will be very slow. Nothing you can do about that, functions are very slow. Try converting it to not use a function.
If it's not in a function, then it's slow because you're calling it once a row for 15000 rows, which is not how SQL works well. Try converting it to set-based code that works per-set, not per-row.
You've got xpath queries, which are also very slow. It looks like they're being used for string split. There are way faster ways to do a string split. Look up DelimitedSplit8k for a faster string split that can operate on multiple rows at the same time.
Or, if you need a fast improvement, one of the advantages to using Azure is that you can scale it up, with a cost.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 29, 2017 at 10:08 am
Is this really something you are running in Azure? If so, what subscription level are you at?
If not in Azure, then what version of SQL Server are you using? It matters.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 29, 2017 at 10:16 am
SQLRNNR - Friday, December 29, 2017 10:08 AMIs this really something you are running in Azure? If so, what subscription level are you at?If not in Azure, then what version of SQL Server are you using? It matters.
Could be 2012 or 2014 as this question was posted in those forums as well.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply