SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Query Taking more time to Run


Query Taking more time to Run

Author
Message
jkramprakash
jkramprakash
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 280
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)SSC Guru (910K reputation)

Group: General Forum Members
Points: 910942 Visits: 48846
https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


jkramprakash
jkramprakash
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 280
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.
ChrisM@home
ChrisM@home
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22137 Visits: 10962
jkramprakash - Sunday, March 4, 2018 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.

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'



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search