Are there are any way to avoid using distinct on dynamic query by using group by ?

Question

I work on SQL server 2012 I face issue using distinct issue is query is very slow after add distinct before add distinct not slow

I NEED to avoid using distinct on dynamic query by using group by or any other thing can do grouping

because when use distinct query become very slow

before adding distinct is more faster

so i need to group data by any way instead of distinct to avoid issues

of performance

SET @Sql= CONCAT('INSERT INTO dbo.AllData(PartID,Code,CodeTypeID,RevisionID,ZPLID ,ConCount,FeatureName,FeatureValue)',' SELECT  PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID,Count(1) as ConCount,
                stuff(( SELECT  ''$'' + CAST( CP.ColumnName AS VARCHAR(300)) AS [text()]
                    FROM(SELECT distinct d.ColumnName,C.codeTypeId,C.Code,C.ZfeatureKey from Condition C 
                    inner join core_datadefinitiondetails d with(nolock) on C.ZfeatureKey=d.columnnumber
                    INNER JOIN PartAttributes P with(nolock) on P.partid=PM.partid)CP
                    where CP.codeTypeId=Co.codeTypeId and CP.Code=Co.Code
                    ORDER BY CP.ZfeatureKey

                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  0, '''') as FeatureName,
                        stuff(( SELECT  ''$'' + CAST( CP2.FeatureValue AS VARCHAR(300)) AS [text()]
                    FROM(SELECT distinct P.FeatureValue,C2.codeTypeId,C2.Code,C2.ZfeatureKey FROM Condition C2 with(nolock)
                    INNER JOIN PartAttributes P with(nolock) on C2.ZfeatureKey=P.ZfeatureKey)CP2
                    where CP2.codeTypeId=Co.codeTypeId and CP2.Code=Co.Code
                    ORDER BY CP2.ZfeatureKey
                    FOR XML PATH(''''), TYPE
                     ).value(''.'', ''NVARCHAR(MAX)'') 
                        , 1,  0, '''') as FeatureValue
                FROM 
                PartAttributes PM 
                INNER JOIN  Condition Co ON Co.ZfeatureKey = PM.ZfeatureKey ',              
                'Where (1=1 and  ',@Con ,  @ConStr,' ) Group By PartID,Code,Co.CodeTypeID,Co.RevisionID,Co.ZPLID ' ,
                ' Having Count(1)>= ',(SELECT COUNT(1) FROM Condition))

            EXEC (@SQL)

Answer 1

Hi @ahmed salah ，

You can use the ROW_NUMBER function instead of DISTINCT to remove duplicates.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".