March 2, 2018 at 7:18 pm
In this stored procedure, the query is taking a lot of time to return the results. Is there any way to make it faster? The tables contains nearly 1 lakshs of records. I mentioned which part taking more time in below query.Please help.
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
-- PDF table
SELECT
PM.F_PRODUCT AS ID,
PM.F_PRODUCT_NAME AS [NAME],
--PM.F_PLANT AS PNT,
--PM.F_FORMAT AS FMT,
--PM.F_SUBFORMAT AS SFMT,
--PM.F_LANGUAGE AS LANG,
(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 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 PM.F_PRODUCT = A.F_ALIAS
FOR XML PATH(''), TYPE
).value( '.', 'NVARCHAR(MAX)' ), 1, 2, '' ) F_DATA
FROM T_REP_PROD RPDS
INNER JOIN (
SELECT DISTINCT F_CUSTOM2 FROM T_TYPES WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2!='')
) MT
ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE AND 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 (
@C2 IS NULL
OR PM.F_CUSTOM2 LIKE @CUSTOM2
OR (
@C2 IS NOT NULL
AND
EXISTS(
SELECT 1 FROM (
SELECT DISTINCT
F_ALIAS,
F_DATA_CODE,
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 PM.F_PRODUCT = A.F_ALIAS
FOR XML PATH(''), TYPE
).value( '.', 'NVARCHAR(MAX)' ), 1, 2, '' ) F_DATA
FROM T_REP_PROD RPDS
INNER JOIN (
SELECT DISTINCT F_CUSTOM2 FROM T_TYPES WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2!='')
) MT
ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE and RPDS.F_ALIAS = PM.F_PRODUCT
) RPDS_CUSTOM2
WHERE RPDS_CUSTOM2.F_DATA LIKE @c2
)
)
)
AND PM.F_AUTHORIZED != 0
AND (@PRDGRP IS NULL OR PG.F_PRODUCT_GROUP LIKE @PRDGRP OR @PRDGRP = '-1')
AND PG.F_PRODUCT_GROUP LIKE '%WV'
;
GO
March 3, 2018 at 9:08 am
Specifically the second section
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2018 at 3:52 am
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.
March 4, 2018 at 4:30 am
jkramprakash - Sunday, March 4, 2018 3:52 AMAND 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.
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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy