question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked EchoLiu-msft commented

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

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)



sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft commented

Hi @ahmedsalah-1628

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".

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can you show me how please

0 Votes 0 ·

can any one help me

0 Votes 0 ·

Sorry for the late reply.I tried to add ROW_NUMBER, but the complicated dynamic SQL gave me a headache.I think it might be more convenient to improve the efficiency when using DISTINCT. Can you tell me how much time it takes to use DISTINCT?

0 Votes 0 ·