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'
the above part in our original post only taking more time.please give some suggestion to rewrite the query.
Since it's repeated anyway, remove it from the main query and materialize as a temp table. You might have to mess around with it to make it work properly - not sample data to work with. Personally I wouldn't consider this a final step in tuning the query, there's too much wrong with it. You will have to examine the execution plan of both queries for that.
CREATE PROCEDURE [dbo].[SEARCH]
@P_NAME VARCHAR(2000),
@S_FORMAT VARCHAR(4),
@LANGUAGE VARCHAR(2),
@C1 VARCHAR(200),
@C2 VARCHAR(200),
@PRDGRP VARCHAR(200)
AS
SELECT --DISTINCT
RPDS.F_ALIAS,
STUFF((
SELECT ', ' + a.F_DATA
FROM T_REP_PROD a
WHERE a.F_ALIAS = RPDS.F_ALIAS
AND a.F_DATA_CODE = RPDS.F_DATA_CODE
AND a.F_ALIAS = PM.F_PRODUCT
FOR XML PATH(''),TYPE).value( '.', 'NVARCHAR(MAX)'
), 1, 2, '' ) F_DATA
INTO #RPDS_CUSTOM2
FROM T_REP_PROD RPDS
WHERE EXISTS (SELECT 1 FROM T_TYPES ti WHERE ti.F_CUSTOM2 = RPDS.F_DATA_CODE)
AND RPDS.F_ALIAS = PM.F_PRODUCT
GROUP BY RPDS.F_ALIAS
-- PDF table
SELECT
PM.F_PRODUCT AS ID,
PM.F_PRODUCT_NAME AS [NAME],
(SELECT F_COMPANY_NAME FROM T_PLANT TPL WHERE TPL.F_PLANT = PM.F_PLANT) AS PNT,
(SELECT F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = PM.F_FORMAT) AS FMT,
(SELECT PM.F_SUBFORMAT + ' - ' + F_MSDSTYPE_DESC FROM T_TYPES MS WHERE MS.F_MSDSTYPE = PM.F_SUBFORMAT AND MS.F_FORMAT = PM.F_FORMAT) AS SFMT,
(SELECT F_LANGUAGE_NAME FROM T_LANG TL WHERE TL.F_LANGUAGE = PM.F_LANGUAGE) AS LANG,
PM.F_DOC_PATH AS DPATH,
CONVERT(VARCHAR,PM.F_PUBLISHED_DATE,120) AS PDATE,
CONVERT(VARCHAR,PM.F_DATE_REVISED,120) AS RDATE,
CONVERT(VARCHAR,PM.F_ISSUE_DATE,120) AS IDATE,
PM.F_AUTHORIZED AS AUT,
PM.F_CUSTOM1 AS cus1,
PM.F_CUSTOM2 AS cus2,
-- ATTENTION: This will drastically slow down the query!
SUBSTRING(
(
CASE WHEN PM.F_CUSTOM2 IS NULL OR PM.F_CUSTOM2 = '' THEN '' ELSE PM.F_CUSTOM2 + ', ' END
+
ISNULL(
(
SELECT TOP 1 F_DATA FROM (
SELECT RPDS.F_DATA
FROM #RPDS_CUSTOM2 RPDS
WHERE RPDS.F_ALIAS = PM.F_PRODUCT
) A
)
, '')
)
, 1, 200) AS cus2,
UPPER(PG.F_PRODUCT_GROUP) AS PGROUP,
PGN.F_PROD_GROUP_NAME AS PGROUPN,
PM.F_REV_NUM AS REVNUM,
PAN.F_ALIAS AS ALIAS,
'Download PDF' AS DOC,
CAST(F_GUID AS VARCHAR(36))+'_PDF' AS [GUID]
FROM T_PDF PM
INNER JOIN T_PRODUCT_ALIAS_NAMES PAN
ON PM.F_PRODUCT = PAN.F_ALIAS
INNER JOIN T_PROD_GROUPING PG
ON PG.F_PRODUCT = PAN.F_PRODUCT
INNER JOIN T_PRODUCT_GROUPS PGN
ON PG.F_PRODUCT_GROUP = PGN.F_PRODUCT_GROUP
WHERE
(@P_NAME IS NULL OR PM.F_PRODUCT_NAME LIKE @P_NAME)
AND (@S_FORMAT IS NULL OR PM.F_SUBFORMAT = @S_FORMAT OR @S_FORMAT = '-1')
AND (@LANGUAGE IS NULL OR PM.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
AND (@C1 IS NULL OR PM.F_CUSTOM1 LIKE @C1)
AND ( -- a
@C2 IS NULL
OR PM.F_CUSTOM2 LIKE @CUSTOM2
OR ( -- b
@C2 IS NOT NULL AND EXISTS ( -- c
SELECT 1 FROM #RPDS_CUSTOM2 RPDS_CUSTOM2
WHERE RPDS_CUSTOM2.F_DATA LIKE @c2 -- Is LIKE appropriate here?
AND RPDS_CUSTOM2.F_ALIAS = PM.F_PRODUCT
) -- c
) -- b
) -- a
AND PM.F_AUTHORIZED != 0
AND (@PRDGRP IS NULL OR PG.F_PRODUCT_GROUP LIKE @PRDGRP OR @PRDGRP = '-1') -- Is LIKE appropriate here?
AND PG.F_PRODUCT_GROUP LIKE '%WV'
[font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and
(II)[/url]
Paul White[/url]
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]