In clause comma separated string function alernate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

In clause comma separated string function alernate

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-04-05 : 02:38:10

I am currently using following function for comma separated string into In clause

ALTER FUNCTION [dbo].[GetCommaSplitValue] (@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)

select @idx = 1
if len(@String)<1 or @String is null return

while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String

if(len(@slice)>0)
insert into @temptable(Items) values(@slice)

set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end

But in certain cases it is taking too long

and ( csm.csmContainerStatus in (SELECT items FROM dbo.GetCommaSplitValue(@CSVValue, ','))

Please guide me a better version for performance improvement

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2012-04-05 : 03:02:29

My query is

declare @BranchID INT
declare @CSVValue varchar(max)
set @BranchID = 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
SET NOCOUNT ON;

SELECT imj.JobID,
imj.imjOriginalJobID,
imjJobNameTitleAndIssue,
imj.imjIdeAllianceVersion,
sum(case
when csm.csmContainerStatus is null then 1
else 0
end) as NotClosed,
sum(case
when csm.csmContainerStatus = 'O' then 1
else 0
end) as Original,
sum(case
when csm.csmContainerStatus = 'P' then 1
else 0
end) as PreliminaryPostage,
sum(case
when csm.csmContainerStatus = 'R' then 1
else 0
end) as ReadyToPay,
sum(case
when csm.csmContainerStatus = 'X' then 1
else 0
end) as Paid,
sum(case
when csm.csmEntryPointActualOrDeliveryLocaleKey = 'ORIGIN' then 1
else 0
end) as OriginEntry,
sum(case
when csm.csmEntryPointActualOrDeliveryLocaleKey <> 'ORIGIN' then 1
else 0
end) as DropShipment,
( count(distinct csm.csmContainerID) ) as TotalPallets,
segClassDefiningPreparation,
segPrincipalProcessingCategory,
( count(distinct case
when csm.csmEntryPointActualOrDeliveryLocaleKey = 'origin' then null
else csm.csmEntryPointActualOrDeliveryLocaleKey
end) ) as MarkedEPs,
imj.imjSubmissionDate,
imjIsLocked,
imjLockedBy
,dbo.GetJobValidationStatus(imj.fkJobValidationStatusID, imj.fkPostalOneValidationStatusId) as JobMailDatPOValidationStatus
FROM ImportJob imj
INNER JOIN SegmentRecord seg
ON imj.JobID = seg.fkJobID
INNER JOIN ContainerSummaryRecord csm
ON seg.fkJobID = csm.fkJobID
AND seg.segSegmentID = csm.fkSegmentID
where imjIsDeleted = 0
and imj.fkBranchID = @BranchID
and imjIsThisAReplicaJob = 0
and ( imjIsUpgraded = 0
or imjIsUpgraded is NULL )
and segIsDeleted = 0
and segClassDefiningPreparation = '3'
and segPrincipalProcessingCategory in ( 'FL', 'LT', 'MP', 'IR' )
and csm.csmIsDeleted = 0
and csm.csmContainerType = 'P'
and csm.csmFASTContentID is null
and csm.csmReservationNumber is null
and ( csm.csmContainerStatus in (SELECT items FROM dbo.GetCommaSplitValue(@CSVValue, ','))
--and ( csm.csmContainerStatus in ('O','P','R','X')
--or csm.csmContainerStatus is null
)
and ( exists (select top 1 fkContainerID
From ContainerQuantityRecord cqt
where cqt.fkJobID = csm.fkJobID
and csm.csmIsDeleted = 0
and csm.csmContainerID = cqt.fkContainerID
and cqt.cqtIsDeleted = 0)
or exists (select top 1 fkContainerID
From ContainerQuantityRecord cqt
INNER JOIN ContainerSummaryRecord csmChild
On csmChild.fkJobID = csm.fkJobID
and csmChild.fkSegmentID = csm.fkSegmentID
and csmChild.csmParentContainerReferenceID = csm.csmContainerID
and cqt.fkJobID = csmChild.fkJobID
and csm.csmIsDeleted = 0
and csmChild.csmIsDeleted = 0
where csmChild.csmContainerID = cqt.fkContainerID
and cqt.cqtIsDeleted = 0))
group by imj.JobID,
imj.imjOriginalJobID,
imj.imjIdeAllianceVersion,
imjJobNameTitleAndIssue,
segClassDefiningPreparation,
segPrincipalProcessingCategory,
imj.imjSubmissionDate,
imjIsLocked,
imjLockedBy
,dbo.GetJobValidationStatus(imj.fkJobValidationStatusID, imj.fkPostalOneValidationStatusId)

And CommaseparatedString to item function is

ALTER FUNCTION [dbo].[GetCommaSplitValue] (@String varchar(8000),
@Delimiter char(1))
returns @temptable TABLE (
items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String) < 1
or @String is null
return
while @idx != 0
begin
set @idx = charindex(@Delimiter, @String)
if @idx != 0
set @slice = left(@String, @idx - 1)
else
set @slice = @String
if( len(@slice) > 0 )
insert into @temptable
(Items)
values (@slice)
set @String = right(@String, len(@String) - @idx)
if len(@String) = 0
break
end
return

end

Now problematic line in my query is

and ( csm.csmContainerStatus in (SELECT items FROM dbo.GetCommaSplitValue(@CSVValue, ','))
--and ( csm.csmContainerStatus in ('O','P','R','X')

fist line with in line function execute in 42 seconds while with same values in In clause hard coded

it takes just 3 odd seconds

Kamran Shahid
Principle Engineer Development
(MCSD.Net,MCPD.net)

malachi151
Posting Yak Master

152 Posts

Posted - 2012-04-06 : 10:22:14

Check out the following thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=171853

Also, you might try parsing the string and inserting the values into a temporary table or table variable first and then doing a SELECT from that instead of parsing the string in line.

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2012-04-08 : 18:05:57

quote:
Originally posted by kamii47

I am currently using following function for comma separated string into In clause

I suspect that a major part of the problem is that you're not actually using an "inline" Table Valued Function (iTVF). Any function that uses a WHILE loop to split strings is actually a "multi-line" Table Valued Function (mTVF) and mTVFs have the same problem as many scalar UDFs... they're not included in the execution plan.

Further, splitter functions with a WHILE loop in them tend to be slow from the git.

My recommendation would be to try the splitter from the "Resources" section at the bottom of the following article. The article also shows the relative performance curve NOT including the performance problem of doing a "join" to an mTVF, as you have, instead of a real iTVF. AND, the performance curves don't actually include the newer function (from the "Resources" section) which is about 10 to 20% faster still.
http://www.sqlservercentral.com/articles/Tally+Table/72993/

--Jeff Moden

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-09 : 06:58:57

http://www.sqlservercentral.com/articles/Tally+Table/72993/

yup...this is the one....by Mr. Jeff Moden himself. The best and most efficient use of Tally Table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources