Click here to Skip to main content
15,299,443 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I work on SQL server 2014 I need to rewrite update statement with best practice
CAN I Write it without subquery ?

What I have tried:

what i try
SET  FamilyGroup = STUFF(
SELECT   DISTINCT '|' + CAST( ISNULL(REPLACE(f.FamilyGroupText,'|','/'),'NULL') AS NVARCHAR(50))
FROM ExtractReports.dbo.MultiMask FF 
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID AND ptt.Masked_ID = FFFFF.Masked_ID
left JOIN [Parts].partscrubbing LC WITH(NOLOCK) ON ptt.PartID=LC.PartID
LEFT JOIN  Parts.ZFamilyGroup f ON f.ID=LC.Generic_3
WHERE ff.Masked_ID=FFFFF.Masked_ID
        FOR XML PATH ('')
FROM  ExtractReports.dbo.MultiMask FFFFF
Updated 4-Jan-22 22:12pm
0x01AA 4-Jan-22 15:47pm
Where is a subquery here?
ahmed_sa 4-Jan-22 15:50pm
thanks for reply
are statement above write best practice correct
ahmed_sa 4-Jan-22 15:51pm
subquery as below
SET FamilyGroup = STUFF(

1 solution

You do seem to be obsessed with character-separated data in columns - which is bad practice by the way.

Based on the other questions you have been posting this is about performance when using FOR XML PATH because it requires a correlated sub-query i.e. this is the bit that is causing the issue
As you have been advised previously - try putting that data into a temporary table so that you are not impacting the larger query e.g.
declare @MultiMaskDemo table (FamilyGroupText varchar(10), Masked_ID int)
insert into @MultiMaskDemo ( FamilyGroupText,Masked_ID) values
('A',1), ('B',1),('C',1),('D',2),('E',2),('F',3);

select F2.Masked_ID, FamilyGroup = STUFF(
	SELECT  '|' + CAST( ISNULL(REPLACE(FamilyGroupText,'|','/'),'NULL') AS NVARCHAR(50))
	FROM @MultiMaskDEMO f1
	WHERE f1.Masked_ID = F2.Masked_ID
    FOR XML PATH ('')
INTO #temp
FROM  @MultiMaskDemo F2
You can then join that temporary table to your main query based on Masked_ID. When you put your joins back into this demo code you can change
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID AND ptt.Masked_ID = FFFFF.Masked_ID
to be
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID
- No need for two references to the outer query.

As an aside, I strongly advise you to stop using character-separated values in columns - create a many-to-one relationship table for storage and only generate the CSV when required (if at all)
Maciej Los 6-Jan-22 14:37pm
You're very kind to this member...
CHill60 6-Jan-22 14:47pm
I'm not sure how long for...
Maciej Los 6-Jan-22 14:48pm

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900