December 29, 2017 at 9:27 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:38 am
Please don't post multiple threads for the same question.
No replies here. Replies to https://www.sqlservercentral.com/Forums/1914800/Need-Help-for-performance
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 9:41 am
Sure, check out this article and discussion: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply