CODEWITHSUNDEEP
sql-server
Ram
Ram

Reputation: 825

Query taking a long time in SQL Server

The below query taking more time to give results. This query in running inside the exists clause and also this query having stuff function.Is there any way to optimize or rewrite this query?.

AND EXISTS(SELECT 1 
           FROM 
               (SELECT DISTINCT
                    F_ALIAS,
                    F_DATA_CODE,
                    STUFF((SELECT ', ' + A.F_DATA
                           FROM T_REP_DATA A
                           WHERE A.F_ALIAS = RPDS.F_ALIAS
                             AND A.F_DATA_CODE = RPDS.F_DATA_CODE
                             AND '1:WVTST{PROD006' = A.F_ALIAS
                           FOR XML PATH(''), TYPE).value( '.', 'NVARCHAR(MAX)'), 1, 2, '') F_DATA
                FROM T_REP_DATA RPDS
                INNER JOIN 
                    (SELECT DISTINCT F_CUSTOM2 
                     FROM T_MSDSTYPES 
                     WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2 != '')) MT ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE and RPDS.F_ALIAS = '1:WVTST{PROD006') RPDS_CUSTOM2)             
           WHERE RPDS_CUSTOM2.F_DATA LIKE 'MANU'

Upvotes: 0

Views: 64

Answers (1)

IVNSTN
IVNSTN

Reputation: 9324

Your query is probably not correct at all (LIKE 'MANU' actually means = 'MANU'), but perhaps your query is doing (or is supposed to be doing) nothing more than this:

AND EXISTS(
  SELECT 1 
  FROM T_REP_DATA RPDS
  INNER JOIN T_MSDSTYPES MT
    ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE
  WHERE RPDS.F_ALIAS = '1:WVTST{PROD006'
    AND RPDS.F_DATA = 'MANU'
)

more specific answer requires more details.

Upvotes: 4

Related Questions