Procedure Result taking more time

Question

Procedure Result taking more time

jkramprakash

SSCommitted

Points: 1870
More actions
August 20, 2018 at 9:42 am

#378299

Below stored procedure taking more time(1 minute 56 seconds) for completing the execution.First table PDF_MSDS only having more records compare to another two tables(HTML_MSDS,RTF_MSDS).I am using union all for combining the results of three tables.I want to optimize this stored procedure.Please help.
ALTER PROCEDURE [dbo].[SEARCH_SDS]
    @PRODUCTID NVARCHAR(50),
    @PRODUCTNAME NVARCHAR(2000),
    @FORMAT NVARCHAR(3),
    @SUBFORMAT NVARCHAR(4),
    @LANGUAGE NVARCHAR(2),
    @PLANT NVARCHAR(10),
    @SUPPLIER NVARCHAR(200),
    @CASNUM NVARCHAR(200),
    @COMPID NVARCHAR(200),
    @KEYWORD NVARCHAR(200),
    @PUBLISHEDDATE_START DATETIME,
    @PUBLISHEDDATE_END DATETIME,
    @REVISEDDATE_START DATETIME,
    @REVISEDDATE_END DATETIME,
    @ISSUEDATE_START DATETIME,
    @ISSUEDATE_END DATETIME,
    @DISPOSALDATE_START DATETIME,
    @DISPOSALDATE_END DATETIME,
    @CUSTOM1 NVARCHAR(200),
    @CUSTOM2 NVARCHAR(200),
    @CUSTOM3 NVARCHAR(200),
    @CUSTOM4 NVARCHAR(200),
    @CUSTOM5 NVARCHAR(200),
    @GUID NVARCHAR(200)
AS
    --START - PDF table
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (SELECT TPL.F_COMPANY_NAME FROM T_PLANT_DEFAULTS TPL WHERE TPL.F_PLANT = TP.F_PLANT) AS PNT,
        (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TP.F_FORMAT) AS FMT,
        (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TP.F_SUBFORMAT AND MS.F_FORMAT = TP.F_FORMAT) AS SFMT,
        (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) AS LANG,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_LANGUAGE AS LANGCODE,
        CONVERT(VARCHAR,TP.F_PUBLISHED_DATE,120) AS PDATE,
        CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
        '' AS COVER,
        CASE
            WHEN EXISTS(SELECT 1 FROM T_DOCUMENTS TD WHERE TD.F_ALIAS = TP.F_PRODUCT AND TD.F_LANGUAGE = TP.F_LANGUAGE) THEN 'Related Document'    ELSE ''
        END AS RDOC,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
        'PDF' AS SDS
    FROM PDF_MSDS TP
    WHERE
        (@PRODUCTID IS NULL OR TP.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID,'[','\['),'_','\_') ESCAPE '\')
        AND (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\')
        AND (@FORMAT IS NULL OR TP.F_FORMAT = @FORMAT)
        AND (@SUBFORMAT IS NULL OR TP.F_SUBFORMAT = @SUBFORMAT)
        AND (TP.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE FROM T_MSDSTYPES MS WHERE NOT(MS.F_MSDSTYPE_DESC = '' OR MS.F_MSDSTYPE_DESC IS NULL) AND MS.F_FORMAT = TP.F_FORMAT))
        AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE)
        AND (@PLANT IS NULL OR TP.F_PLANT = @PLANT)
        AND (@SUPPLIER IS NULL OR TP.F_SUPPLIER LIKE @SUPPLIER)
        AND (@CASNUM IS NULL OR TP.F_CAS_NUMBERS LIKE @CASNUM)
        AND (@COMPID IS NULL OR TP.F_COMPONENT_IDS LIKE @COMPID)
        AND (@KEYWORD IS NULL OR TP.F_KEYWORDS LIKE @KEYWORD)
        AND (@PUBLISHEDDATE_START IS NULL OR TP.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END)
        AND (@REVISEDDATE_START IS NULL OR TP.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END)
        AND (@ISSUEDATE_START IS NULL OR TP.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END)
        AND (@DISPOSALDATE_START IS NULL OR TP.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END )
        AND (@CUSTOM1 IS NULL OR TP.F_CUSTOM1 LIKE @CUSTOM1)
        AND (@CUSTOM2 IS NULL OR TP.F_CUSTOM2 LIKE @CUSTOM2)
        AND (@CUSTOM3 IS NULL OR TP.F_CUSTOM3 LIKE @CUSTOM3)
        AND (@CUSTOM4 IS NULL OR TP.F_CUSTOM4 LIKE @CUSTOM4)
        AND (@CUSTOM5 IS NULL OR TP.F_CUSTOM5 LIKE @CUSTOM5)
        AND TP.F_AUTHORIZED <> 0
UNION ALL
--START - RTF table
    SELECT DISTINCT TR.F_PRODUCT AS ID,
        TR.F_PRODUCT_NAME AS [NAME],
        (SELECT TPL.F_COMPANY_NAME FROM T_PLANT_DEFAULTS TPL WHERE TPL.F_PLANT = TR.F_PLANT) AS PNT,
        (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TR.F_FORMAT) AS FMT,
        (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TR.F_SUBFORMAT AND MS.F_FORMAT = TR.F_FORMAT) AS SFMT,
        (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TR.F_LANGUAGE) AS LANG,
        TR.F_PLANT AS PLANTCODE,
        TR.F_FORMAT AS FMTCODE,
        TR.F_SUBFORMAT AS SFMTCODE,
        TR.F_LANGUAGE AS LANGCODE,
        CONVERT(VARCHAR,TR.F_PUBLISHED_DATE,120) AS PDATE,
        CONVERT(VARCHAR,TR.F_DATE_REVISED,120) AS RDATE,
        TR.F_CUSTOM1 AS cus1,
        TR.F_CUSTOM2 AS cus2,
        'SDS - ' + (SELECT F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TR.F_LANGUAGE) + ' - RTF' AS DOC,
        '' AS COVER,
        CASE
            WHEN EXISTS(SELECT 1 FROM T_DOCUMENTS TD WHERE TD.F_ALIAS = TR.F_PRODUCT AND TD.F_LANGUAGE = TR.F_LANGUAGE) THEN 'Related Document'    ELSE ''
        END AS RDOC,
        CAST(F_GUID AS VARCHAR(36)) + '_RTF' AS [GUID],
        'RTF' AS SDS
    FROM RTF_MSDS TR
    WHERE
        (@PRODUCTID IS NULL OR TR.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID,'[','\['),'_','\_') ESCAPE '\')
        AND (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TR.F_PRODUCT_NAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\')
        AND (@FORMAT IS NULL OR TR.F_FORMAT = @FORMAT)
        AND (@SUBFORMAT IS NULL OR TR.F_SUBFORMAT = @SUBFORMAT)
        AND (TR.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE FROM T_MSDSTYPES MS WHERE NOT(MS.F_MSDSTYPE_DESC = '' OR MS.F_MSDSTYPE_DESC IS NULL) AND MS.F_FORMAT = TR.F_FORMAT))
        AND (@LANGUAGE IS NULL OR TR.F_LANGUAGE = @LANGUAGE)
        AND (@PLANT IS NULL OR TR.F_PLANT = @PLANT)
        AND (@SUPPLIER IS NULL OR TR.F_SUPPLIER LIKE @SUPPLIER)
        AND (@CASNUM IS NULL OR TR.F_CAS_NUMBERS LIKE @CASNUM)
        AND (@COMPID IS NULL OR TR.F_COMPONENT_IDS LIKE @COMPID)
        AND (@KEYWORD IS NULL OR TR.F_KEYWORDS LIKE @KEYWORD)
        AND (@PUBLISHEDDATE_START IS NULL OR TR.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END)
        AND (@REVISEDDATE_START IS NULL OR TR.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END)
        AND (@ISSUEDATE_START IS NULL OR TR.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END)
        AND (@DISPOSALDATE_START IS NULL OR TR.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END )
        AND (@CUSTOM1 IS NULL OR TR.F_CUSTOM1 LIKE @CUSTOM1)
        AND (@CUSTOM2 IS NULL OR TR.F_CUSTOM2 LIKE @CUSTOM2)
        AND (@CUSTOM3 IS NULL OR TR.F_CUSTOM3 LIKE @CUSTOM3)
        AND (@CUSTOM4 IS NULL OR TR.F_CUSTOM4 LIKE @CUSTOM4)
        AND (@CUSTOM5 IS NULL OR TR.F_CUSTOM5 LIKE @CUSTOM5)
        AND TR.F_AUTHORIZED <> 0
    UNION ALL

--START - HTML table
    SELECT DISTINCT TH.F_PRODUCT AS ID,
        TH.F_PRODUCT_NAME AS [NAME],
        (SELECT TPL.F_COMPANY_NAME FROM T_PLANT_DEFAULTS TPL WHERE TPL.F_PLANT = TH.F_PLANT) AS PNT,
        (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TH.F_FORMAT) AS FMT,
        (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TH.F_SUBFORMAT AND MS.F_FORMAT = TH.F_FORMAT) AS SFMT,
        (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) AS LANG,
        TH.F_PLANT AS PLANTCODE,
        TH.F_FORMAT AS FMTCODE,
        TH.F_SUBFORMAT AS SFMTCODE,
        TH.F_LANGUAGE AS LANGCODE,
        CONVERT(VARCHAR,TH.F_PUBLISHED_DATE,120) AS PDATE,
        CONVERT(VARCHAR,TH.F_DATE_REVISED,120) AS RDATE,
        TH.F_CUSTOM1 AS cus1,
        TH.F_CUSTOM2 AS cus2,
        CASE
            WHEN NOT(TDF.F_FILE_NAME IS NULL OR TDF.F_FILE_NAME = '') THEN
                'SDS - ' + (SELECT F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TH.F_LANGUAGE)+ ' - ' + REVERSE(LEFT(REVERSE(TDF.F_FILE_NAME), CHARINDEX('.', REVERSE(TDF.F_FILE_NAME)) -1))
            ELSE ''
        END AS DOC,
        'COVER - ' + (SELECT F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) + ' - HTML' AS COVER,
        CASE
            WHEN EXISTS(SELECT 1 FROM T_DOCUMENTS TD WHERE TD.F_ALIAS = TH.F_PRODUCT AND TD.F_LANGUAGE = TH.F_LANGUAGE) THEN 'Related Document'    ELSE ''
        END AS RDOC,
        CAST(F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM HTML_MSDS TH
    LEFT JOIN T_DW_FILES TDF
         ON TH.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    WHERE
        (@PRODUCTID IS NULL OR TH.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID,'[','\['),'_','\_') ESCAPE '\')
        AND (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TH.F_PRODUCT_NAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\')
        AND (@FORMAT IS NULL OR TH.F_FORMAT = @FORMAT)
        AND (@SUBFORMAT IS NULL OR TH.F_SUBFORMAT = @SUBFORMAT OR @SUBFORMAT = '-1')
        AND (TH.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE FROM T_MSDSTYPES MS WHERE NOT(MS.F_MSDSTYPE_DESC = '' OR MS.F_MSDSTYPE_DESC IS NULL) AND MS.F_FORMAT = TH.F_FORMAT))
        AND (@LANGUAGE IS NULL OR TH.F_LANGUAGE = @LANGUAGE)
        AND (@PLANT IS NULL OR TH.F_PLANT = @PLANT)
        AND (@SUPPLIER IS NULL OR TH.F_SUPPLIER LIKE @SUPPLIER)
        AND (@CASNUM IS NULL OR TH.F_CAS_NUMBERS LIKE @CASNUM)
        AND (@COMPID IS NULL OR TH.F_COMPONENT_IDS LIKE @COMPID)
        AND (@KEYWORD IS NULL OR TH.F_KEYWORDS LIKE @KEYWORD)
        AND (@PUBLISHEDDATE_START IS NULL OR TH.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END)
        AND (@REVISEDDATE_START IS NULL OR TH.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END)
        AND (@ISSUEDATE_START IS NULL OR TH.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END)
        AND (@DISPOSALDATE_START IS NULL OR TH.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END )
        AND (@CUSTOM1 IS NULL OR TH.F_CUSTOM1 LIKE @CUSTOM1)
        AND (@CUSTOM2 IS NULL OR TH.F_CUSTOM2 LIKE @CUSTOM2)
        AND (@CUSTOM3 IS NULL OR TH.F_CUSTOM3 LIKE @CUSTOM3)
        AND (@CUSTOM4 IS NULL OR TH.F_CUSTOM4 LIKE @CUSTOM4)
        AND (@CUSTOM5 IS NULL OR TH.F_CUSTOM5 LIKE @CUSTOM5)
        AND TH.F_AUTHORIZED <> 0
    ;

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

That looks like a huge catch-all query, so I'm really not surprised it's running poorly. You'll likely want to change this to dynamic SQL. Gail wrote a great article on this a few years ago, which I've linked below. You also have a quite a few subqueries that are in the SELECT, can you not move those to the FROM clause using a JOIN? That'll likely help in performance too.

Have a read of the article, and if you have any questions, please do ask.

https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 2

Thom A - Monday, August 20, 2018 9:56 AM
That looks like a huge catch-all query, so I'm really not surprised it's running poorly. You'll likely want to change this to dynamic SQL. Gail wrote a great article on this a few years ago, which I've linked below. Have a read of that, and if you have any questions, please do ask.
https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Before you rewrite it all as dynamic SQL try adding:
OPTION(OPTIMIZE FOR UNKNOWN)
at the end of the SQL that's taking a long time to execute.

Thom A SSC Guru Points: 99130 More actions · Answer 3

For sanity, I've done a little formatting to the file, however, I've uploaded as a txt file. SSC would also destroy any whitespace I added due to it's "unique" pasting interpretation (which is yet to be addressed...).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jkramprakash SSCommitted Points: 1870 More actions · Answer 4

Again i am posting with proper alignment.Now i used OPTION (OPTIMIZE FOR UNKNOWN) in my first part at the end of query.Is it correct?.Whether i have to use in all the queries at the end?

ALTER PROCEDURE [dbo].[SEARCH_SDS] @PRODUCTID nvarchar(50),
            @PRODUCTNAME nvarchar(2000),
            @FORMAT nvarchar(3),
            @SUBFORMAT nvarchar(4),
            @LANGUAGE nvarchar(2),
            @PLANT nvarchar(10),
            @SUPPLIER nvarchar(200),
            @CASNUM nvarchar(200),
            @COMPID nvarchar(200),
            @KEYWORD nvarchar(200),
            @PUBLISHEDDATE_START datetime,
            @PUBLISHEDDATE_END datetime,
            @REVISEDDATE_START datetime,
            @REVISEDDATE_END datetime,
            @ISSUEDATE_START datetime,
            @ISSUEDATE_END datetime,
            @DISPOSALDATE_START datetime,
            @DISPOSALDATE_END datetime,
            @CUSTOM1 nvarchar(200),
            @CUSTOM2 nvarchar(200),
            @CUSTOM3 nvarchar(200),
            @CUSTOM4 nvarchar(200),
            @CUSTOM5 nvarchar(200),
            @GUID nvarchar(200)
AS
--START - PDF table
SELECT DISTINCT
    TP.F_PRODUCT AS ID,
    TP.F_PRODUCT_NAME AS [NAME],
    (SELECT TPL.F_COMPANY_NAME
    FROM T_PLANT_DEFAULTS TPL
    WHERE TPL.F_PLANT = TP.F_PLANT) AS PNT,
    (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TP.F_FORMAT) AS FMT,
    (SELECT MS.F_MSDSTYPE_DESC
    FROM T_MSDSTYPES MS
    WHERE MS.F_MSDSTYPE = TP.F_SUBFORMAT
     AND MS.F_FORMAT = TP.F_FORMAT) AS SFMT,
    (SELECT TL.F_LANGUAGE_NAME
    FROM T_LANGUAGES TL
    WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) AS LANG,
    TP.F_PLANT AS PLANTCODE,
    TP.F_FORMAT AS FMTCODE,
    TP.F_SUBFORMAT AS SFMTCODE,
    TP.F_LANGUAGE AS LANGCODE,
    CONVERT(varchar, TP.F_PUBLISHED_DATE, 120) AS PDATE,
    CONVERT(varchar, TP.F_DATE_REVISED, 120) AS RDATE,
    TP.F_CUSTOM1 AS cus1,
    TP.F_CUSTOM2 AS cus2,
    'SDS - ' + (SELECT TL.F_LANGUAGE_NAME
        FROM T_LANGUAGES TL
        WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
    '' AS COVER,
    CASE
      WHEN EXISTS (SELECT 1
          FROM T_DOCUMENTS TD
          WHERE TD.F_ALIAS = TP.F_PRODUCT
           AND TD.F_LANGUAGE = TP.F_LANGUAGE) THEN 'Related Document'
      ELSE ''
    END AS RDOC,
    CAST(TP.F_GUID AS varchar(36)) + '_PDF' AS [GUID],
    'PDF' AS SDS
FROM PDF_MSDS TP
WHERE (@PRODUCTID IS NULL
   OR TP.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID, '[', '\['), '_', '\_')ESCAPE '\')
  AND (@PRODUCTNAME IS NULL
   OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME, '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG') LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME, '[', '\['), '_', '\_'), '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG')ESCAPE '\')
  AND (@FORMAT IS NULL
   OR TP.F_FORMAT = @FORMAT)
  AND (@SUBFORMAT IS NULL
   OR TP.F_SUBFORMAT = @SUBFORMAT)
  AND (TP.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE
          FROM T_MSDSTYPES MS
          WHERE NOT (MS.F_MSDSTYPE_DESC = ''
             OR MS.F_MSDSTYPE_DESC IS NULL)
           AND MS.F_FORMAT = TP.F_FORMAT))
  AND (@LANGUAGE IS NULL
   OR TP.F_LANGUAGE = @LANGUAGE)
  AND (@PLANT IS NULL
   OR TP.F_PLANT = @PLANT)
  AND (@SUPPLIER IS NULL
   OR TP.F_SUPPLIER LIKE @SUPPLIER)
  AND (@CASNUM IS NULL
   OR TP.F_CAS_NUMBERS LIKE @CASNUM)
  AND (@COMPID IS NULL
   OR TP.F_COMPONENT_IDS LIKE @COMPID)
  AND (@KEYWORD IS NULL
   OR TP.F_KEYWORDS LIKE @KEYWORD)
  AND (@PUBLISHEDDATE_START IS NULL
   OR TP.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END)
  AND (@REVISEDDATE_START IS NULL
   OR TP.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END)
  AND (@ISSUEDATE_START IS NULL
   OR TP.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END)
  AND (@DISPOSALDATE_START IS NULL
   OR TP.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END)
  AND (@CUSTOM1 IS NULL
   OR TP.F_CUSTOM1 LIKE @CUSTOM1)
  AND (@CUSTOM2 IS NULL
   OR TP.F_CUSTOM2 LIKE @CUSTOM2)
  AND (@CUSTOM3 IS NULL
   OR TP.F_CUSTOM3 LIKE @CUSTOM3)
  AND (@CUSTOM4 IS NULL
   OR TP.F_CUSTOM4 LIKE @CUSTOM4)
  AND (@CUSTOM5 IS NULL
   OR TP.F_CUSTOM5 LIKE @CUSTOM5)
  AND TP.F_AUTHORIZED <> 0
OPTION (OPTIMIZE FOR UNKNOWN)
UNION ALL
OPTION (OPTIMIZE FOR UNKNOWN)
--START - RTF table
SELECT DISTINCT
    TR.F_PRODUCT AS ID,
    TR.F_PRODUCT_NAME AS [NAME],
    (SELECT TPL.F_COMPANY_NAME
    FROM T_PLANT_DEFAULTS TPL
    WHERE TPL.F_PLANT = TR.F_PLANT) AS PNT,
    (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TR.F_FORMAT) AS FMT,
    (SELECT MS.F_MSDSTYPE_DESC
    FROM T_MSDSTYPES MS
    WHERE MS.F_MSDSTYPE = TR.F_SUBFORMAT
     AND MS.F_FORMAT = TR.F_FORMAT) AS SFMT,
    (SELECT TL.F_LANGUAGE_NAME
    FROM T_LANGUAGES TL
    WHERE TL.F_LANGUAGE = TR.F_LANGUAGE) AS LANG,
    TR.F_PLANT AS PLANTCODE,
    TR.F_FORMAT AS FMTCODE,
    TR.F_SUBFORMAT AS SFMTCODE,
    TR.F_LANGUAGE AS LANGCODE,
    CONVERT(varchar, TR.F_PUBLISHED_DATE, 120) AS PDATE,
    CONVERT(varchar, TR.F_DATE_REVISED, 120) AS RDATE,
    TR.F_CUSTOM1 AS cus1,
    TR.F_CUSTOM2 AS cus2,
    'SDS - ' + (SELECT F_LANGUAGE_NAME
        FROM T_LANGUAGES TL
        WHERE TL.F_LANGUAGE = TR.F_LANGUAGE) + ' - RTF' AS DOC,
    '' AS COVER,
    CASE
      WHEN EXISTS (SELECT 1
          FROM T_DOCUMENTS TD
          WHERE TD.F_ALIAS = TR.F_PRODUCT
           AND TD.F_LANGUAGE = TR.F_LANGUAGE) THEN 'Related Document'
      ELSE ''
    END AS RDOC,
    CAST(F_GUID AS varchar(36)) + '_RTF' AS [GUID],
    'RTF' AS SDS
FROM RTF_MSDS TR
WHERE (@PRODUCTID IS NULL
   OR TR.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID, '[', '\['), '_', '\_')ESCAPE '\')
  AND (@PRODUCTNAME IS NULL
   OR REPLACE(REPLACE(REPLACE(REPLACE(TR.F_PRODUCT_NAME, '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG') LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME, '[', '\['), '_', '\_'), '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG')ESCAPE '\')
  AND (@FORMAT IS NULL
   OR TR.F_FORMAT = @FORMAT)
  AND (@SUBFORMAT IS NULL
   OR TR.F_SUBFORMAT = @SUBFORMAT)
  AND (TR.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE
          FROM T_MSDSTYPES MS
          WHERE NOT (MS.F_MSDSTYPE_DESC = ''
             OR MS.F_MSDSTYPE_DESC IS NULL)
           AND MS.F_FORMAT = TR.F_FORMAT))
  AND (@LANGUAGE IS NULL
   OR TR.F_LANGUAGE = @LANGUAGE)
  AND (@PLANT IS NULL
   OR TR.F_PLANT = @PLANT)
  AND (@SUPPLIER IS NULL
   OR TR.F_SUPPLIER LIKE @SUPPLIER)
  AND (@CASNUM IS NULL
   OR TR.F_CAS_NUMBERS LIKE @CASNUM)
  AND (@COMPID IS NULL
   OR TR.F_COMPONENT_IDS LIKE @COMPID)
  AND (@KEYWORD IS NULL
   OR TR.F_KEYWORDS LIKE @KEYWORD)
  AND (@PUBLISHEDDATE_START IS NULL
   OR TR.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END)
  AND (@REVISEDDATE_START IS NULL
   OR TR.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END)
  AND (@ISSUEDATE_START IS NULL
   OR TR.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END)
  AND (@DISPOSALDATE_START IS NULL
   OR TR.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END)
  AND (@CUSTOM1 IS NULL
   OR TR.F_CUSTOM1 LIKE @CUSTOM1)
  AND (@CUSTOM2 IS NULL
   OR TR.F_CUSTOM2 LIKE @CUSTOM2)
  AND (@CUSTOM3 IS NULL
   OR TR.F_CUSTOM3 LIKE @CUSTOM3)
  AND (@CUSTOM4 IS NULL
   OR TR.F_CUSTOM4 LIKE @CUSTOM4)
  AND (@CUSTOM5 IS NULL
   OR TR.F_CUSTOM5 LIKE @CUSTOM5)
  AND TR.F_AUTHORIZED <> 0
UNION ALL

--START - HTML table
SELECT DISTINCT
    TH.F_PRODUCT AS ID,
    TH.F_PRODUCT_NAME AS [NAME],
    (SELECT TPL.F_COMPANY_NAME
    FROM T_PLANT_DEFAULTS TPL
    WHERE TPL.F_PLANT = TH.F_PLANT) AS PNT,
    (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TH.F_FORMAT) AS FMT,
    (SELECT MS.F_MSDSTYPE_DESC
    FROM T_MSDSTYPES MS
    WHERE MS.F_MSDSTYPE = TH.F_SUBFORMAT
     AND MS.F_FORMAT = TH.F_FORMAT) AS SFMT,
    (SELECT TL.F_LANGUAGE_NAME
    FROM T_LANGUAGES TL
    WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) AS LANG,
    TH.F_PLANT AS PLANTCODE,
    TH.F_FORMAT AS FMTCODE,
    TH.F_SUBFORMAT AS SFMTCODE,
    TH.F_LANGUAGE AS LANGCODE,
    CONVERT(varchar, TH.F_PUBLISHED_DATE, 120) AS PDATE,
    CONVERT(varchar, TH.F_DATE_REVISED, 120) AS RDATE,
    TH.F_CUSTOM1 AS cus1,
    TH.F_CUSTOM2 AS cus2,
    CASE
      WHEN NOT (TDF.F_FILE_NAME IS NULL
        OR TDF.F_FILE_NAME = '') THEN 'SDS - ' + (SELECT F_LANGUAGE_NAME
                      FROM T_LANGUAGES TL
                      WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) + ' - ' + REVERSE(LEFT(REVERSE(TDF.F_FILE_NAME), CHARINDEX('.', REVERSE(TDF.F_FILE_NAME)) - 1))
      ELSE ''
    END AS DOC,
    'COVER - ' + (SELECT F_LANGUAGE_NAME
         FROM T_LANGUAGES TL
         WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) + ' - HTML' AS COVER,
    CASE
      WHEN EXISTS (SELECT 1
          FROM T_DOCUMENTS TD
          WHERE TD.F_ALIAS = TH.F_PRODUCT
           AND TD.F_LANGUAGE = TH.F_LANGUAGE) THEN 'Related Document'
      ELSE ''
    END AS RDOC,
    CAST(F_GUID AS varchar(36)) + '_HTML' AS [GUID],
    'HTML' AS SDS
FROM HTML_MSDS TH
   LEFT JOIN T_DW_FILES TDF ON TH.F_DOC_PATH = CONVERT(varchar(10), TDF.F_RECORD_ID)
WHERE (@PRODUCTID IS NULL
   OR TH.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID, '[', '\['), '_', '\_')ESCAPE '\')
  AND (@PRODUCTNAME IS NULL
   OR REPLACE(REPLACE(REPLACE(REPLACE(TH.F_PRODUCT_NAME, '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG') LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME, '[', '\['), '_', '\_'), '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG')ESCAPE '\')
  AND (@FORMAT IS NULL
   OR TH.F_FORMAT = @FORMAT)
  AND (@SUBFORMAT IS NULL
   OR TH.F_SUBFORMAT = @SUBFORMAT
   OR @SUBFORMAT = '-1')
  AND (TH.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE
          FROM T_MSDSTYPES MS
          WHERE NOT (MS.F_MSDSTYPE_DESC = ''
             OR MS.F_MSDSTYPE_DESC IS NULL)
           AND MS.F_FORMAT = TH.F_FORMAT))
  AND (@LANGUAGE IS NULL
   OR TH.F_LANGUAGE = @LANGUAGE)
  AND (@PLANT IS NULL
   OR TH.F_PLANT = @PLANT)
  AND (@SUPPLIER IS NULL
   OR TH.F_SUPPLIER LIKE @SUPPLIER)
  AND (@CASNUM IS NULL
   OR TH.F_CAS_NUMBERS LIKE @CASNUM)
  AND (@COMPID IS NULL
   OR TH.F_COMPONENT_IDS LIKE @COMPID)
  AND (@KEYWORD IS NULL
   OR TH.F_KEYWORDS LIKE @KEYWORD)
  AND (@PUBLISHEDDATE_START IS NULL
   OR TH.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END)
  AND (@REVISEDDATE_START IS NULL
   OR TH.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END)
  AND (@ISSUEDATE_START IS NULL
   OR TH.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END)
  AND (@DISPOSALDATE_START IS NULL
   OR TH.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END)
  AND (@CUSTOM1 IS NULL
   OR TH.F_CUSTOM1 LIKE @CUSTOM1)
  AND (@CUSTOM2 IS NULL
   OR TH.F_CUSTOM2 LIKE @CUSTOM2)
  AND (@CUSTOM3 IS NULL
   OR TH.F_CUSTOM3 LIKE @CUSTOM3)
  AND (@CUSTOM4 IS NULL
   OR TH.F_CUSTOM4 LIKE @CUSTOM4)
  AND (@CUSTOM5 IS NULL
   OR TH.F_CUSTOM5 LIKE @CUSTOM5)
  AND TH.F_AUTHORIZED <> 0;

Thom A SSC Guru Points: 99130 More actions · Answer 5

jkramprakash - Monday, August 20, 2018 10:47 AM
Again i am posting with proper alignment.Now i used OPTION (OPTIMIZE FOR UNKNOWN) in my first part at the end of query.Is it correct?.Whether i have to use in all the queries at the end?

You're not putting your code inside the bbcode markup. There's a button at the bottom of the panel for formatting:

Then your code looks like this:

jkramprakash - Monday, August 20, 2018 10:47 AM
ALTER PROCEDURE [dbo].[SEARCH_SDS] @PRODUCTID nvarchar(50), @PRODUCTNAME nvarchar(2000), @FORMAT nvarchar(3), @SUBFORMAT nvarchar(4), @LANGUAGE nvarchar(2), @PLANT nvarchar(10), @SUPPLIER nvarchar(200), @CASNUM nvarchar(200), @COMPID nvarchar(200), @KEYWORD nvarchar(200), @PUBLISHEDDATE_START datetime, @PUBLISHEDDATE_END datetime, @REVISEDDATE_START datetime, @REVISEDDATE_END datetime, @ISSUEDATE_START datetime, @ISSUEDATE_END datetime, @DISPOSALDATE_START datetime, @DISPOSALDATE_END datetime, @CUSTOM1 nvarchar(200), @CUSTOM2 nvarchar(200), @CUSTOM3 nvarchar(200), @CUSTOM4 nvarchar(200), @CUSTOM5 nvarchar(200), @GUID nvarchar(200) AS --START - PDF table SELECT DISTINCT TP.F_PRODUCT AS ID, TP.F_PRODUCT_NAME AS [NAME], (SELECT TPL.F_COMPANY_NAME FROM T_PLANT_DEFAULTS TPL WHERE TPL.F_PLANT = TP.F_PLANT) AS PNT, (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TP.F_FORMAT) AS FMT, (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TP.F_SUBFORMAT AND MS.F_FORMAT = TP.F_FORMAT) AS SFMT, (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) AS LANG, TP.F_PLANT AS PLANTCODE, TP.F_FORMAT AS FMTCODE, TP.F_SUBFORMAT AS SFMTCODE, TP.F_LANGUAGE AS LANGCODE, CONVERT(varchar, TP.F_PUBLISHED_DATE, 120) AS PDATE, CONVERT(varchar, TP.F_DATE_REVISED, 120) AS RDATE, TP.F_CUSTOM1 AS cus1, TP.F_CUSTOM2 AS cus2, 'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC, '' AS COVER, CASE WHEN EXISTS (SELECT 1 FROM T_DOCUMENTS TD WHERE TD.F_ALIAS = TP.F_PRODUCT AND TD.F_LANGUAGE = TP.F_LANGUAGE) THEN 'Related Document' ELSE '' END AS RDOC, CAST(TP.F_GUID AS varchar(36)) + '_PDF' AS [GUID], 'PDF' AS SDS FROM PDF_MSDS TP WHERE (@PRODUCTID IS NULL OR TP.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID, '[', '\['), '_', '\_')ESCAPE '\') AND (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME, 'â„¢', '|TM'), 'Â®', '|TS'), 'Â©', '|CP'), 'Â°', '|DEG') LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME, '[', '\['), '_', '\_'), 'â„¢', '|TM'), 'Â®', '|TS'), 'Â©', '|CP'), 'Â°', '|DEG')ESCAPE '\') AND (@FORMAT IS NULL OR TP.F_FORMAT = @FORMAT) AND (@SUBFORMAT IS NULL OR TP.F_SUBFORMAT = @SUBFORMAT) AND (TP.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE FROM T_MSDSTYPES MS WHERE NOT (MS.F_MSDSTYPE_DESC = '' OR MS.F_MSDSTYPE_DESC IS NULL) AND MS.F_FORMAT = TP.F_FORMAT)) AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE) AND (@PLANT IS NULL OR TP.F_PLANT = @PLANT) AND (@SUPPLIER IS NULL OR TP.F_SUPPLIER LIKE @SUPPLIER) AND (@CASNUM IS NULL OR TP.F_CAS_NUMBERS LIKE @CASNUM) AND (@COMPID IS NULL OR TP.F_COMPONENT_IDS LIKE @COMPID) AND (@KEYWORD IS NULL OR TP.F_KEYWORDS LIKE @KEYWORD) AND (@PUBLISHEDDATE_START IS NULL OR TP.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END) AND (@REVISEDDATE_START IS NULL OR TP.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END) AND (@ISSUEDATE_START IS NULL OR TP.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END) AND (@DISPOSALDATE_START IS NULL OR TP.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END) AND (@CUSTOM1 IS NULL OR TP.F_CUSTOM1 LIKE @CUSTOM1) AND (@CUSTOM2 IS NULL OR TP.F_CUSTOM2 LIKE @CUSTOM2) AND (@CUSTOM3 IS NULL OR TP.F_CUSTOM3 LIKE @CUSTOM3) AND (@CUSTOM4 IS NULL OR TP.F_CUSTOM4 LIKE @CUSTOM4) AND (@CUSTOM5 IS NULL OR TP.F_CUSTOM5 LIKE @CUSTOM5) AND TP.F_AUTHORIZED <> 0 OPTION (OPTIMIZE FOR UNKNOWN) UNION ALL OPTION OPTION ((OPTIMIZE UNKNOWNOPTIMIZE FORFOR UNKNOWN)) --START - RTF table SELECT DISTINCT TR.F_PRODUCT AS ID, TR.F_PRODUCT_NAME AS [NAME], (SELECT TPL.F_COMPANY_NAME FROM T_PLANT_DEFAULTS TPL WHERE TPL.F_PLANT = TR.F_PLANT) AS PNT, (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TR.F_FORMAT) AS FMT, (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TR.F_SUBFORMAT AND MS.F_FORMAT = TR.F_FORMAT) AS SFMT, (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TR.F_LANGUAGE) AS LANG, TR.F_PLANT AS PLANTCODE, TR.F_FORMAT AS FMTCODE, TR.F_SUBFORMAT AS SFMTCODE, TR.F_LANGUAGE AS LANGCODE, CONVERT(varchar, TR.F_PUBLISHED_DATE, 120) AS PDATE, CONVERT(varchar, TR.F_DATE_REVISED, 120) AS RDATE, TR.F_CUSTOM1 AS cus1, TR.F_CUSTOM2 AS cus2, 'SDS - ' + (SELECT F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TR.F_LANGUAGE) + ' - RTF' AS DOC, '' AS COVER, CASE WHEN EXISTS (SELECT 1 FROM T_DOCUMENTS TD WHERE TD.F_ALIAS = TR.F_PRODUCT AND TD.F_LANGUAGE = TR.F_LANGUAGE) THEN 'Related Document' ELSE '' END AS RDOC, CAST(F_GUID AS varchar(36)) + '_RTF' AS [GUID], 'RTF' AS SDS FROM RTF_MSDS TR WHERE (@PRODUCTID IS NULL OR TR.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID, '[', '\['), '_', '\_')ESCAPE '\') AND (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TR.F_PRODUCT_NAME, 'â„¢', '|TM'), 'Â®', '|TS'), 'Â©', '|CP'), 'Â°', '|DEG') LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME, '[', '\['), '_', '\_'), 'â„¢', '|TM'), 'Â®', '|TS'), 'Â©', '|CP'), 'Â°', '|DEG')ESCAPE '\') AND (@FORMAT IS NULL OR TR.F_FORMAT = @FORMAT) AND (@SUBFORMAT IS NULL OR TR.F_SUBFORMAT = @SUBFORMAT) AND (TR.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE FROM T_MSDSTYPES MS WHERE NOT (MS.F_MSDSTYPE_DESC = '' OR MS.F_MSDSTYPE_DESC IS NULL) AND MS.F_FORMAT = TR.F_FORMAT)) AND (@LANGUAGE IS NULL OR TR.F_LANGUAGE = @LANGUAGE) AND (@PLANT IS NULL OR TR.F_PLANT = @PLANT) AND (@SUPPLIER IS NULL OR TR.F_SUPPLIER LIKE @SUPPLIER) AND (@CASNUM IS NULL OR TR.F_CAS_NUMBERS LIKE @CASNUM) AND (@COMPID IS NULL OR TR.F_COMPONENT_IDS LIKE @COMPID) AND (@KEYWORD IS NULL OR TR.F_KEYWORDS LIKE @KEYWORD) AND (@PUBLISHEDDATE_START IS NULL OR TR.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END) AND (@REVISEDDATE_START IS NULL OR TR.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END) AND (@ISSUEDATE_START IS NULL OR TR.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END) AND (@DISPOSALDATE_START IS NULL OR TR.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END) AND (@CUSTOM1 IS NULL OR TR.F_CUSTOM1 LIKE @CUSTOM1) AND (@CUSTOM2 IS NULL OR TR.F_CUSTOM2 LIKE @CUSTOM2) AND (@CUSTOM3 IS NULL OR TR.F_CUSTOM3 LIKE @CUSTOM3) AND (@CUSTOM4 IS NULL OR TR.F_CUSTOM4 LIKE @CUSTOM4) AND (@CUSTOM5 IS NULL OR TR.F_CUSTOM5 LIKE @CUSTOM5) AND TR.F_AUTHORIZED <> 0 UNION ALL
--START - HTML table SELECT DISTINCT TH.F_PRODUCT AS ID, TH.F_PRODUCT_NAME AS [NAME], (SELECT TPL.F_COMPANY_NAME FROM T_PLANT_DEFAULTS TPL WHERE TPL.F_PLANT = TH.F_PLANT) AS PNT, (SELECT TF.F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = TH.F_FORMAT) AS FMT, (SELECT MS.F_MSDSTYPE_DESC FROM T_MSDSTYPES MS WHERE MS.F_MSDSTYPE = TH.F_SUBFORMAT AND MS.F_FORMAT = TH.F_FORMAT) AS SFMT, (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) AS LANG, TH.F_PLANT AS PLANTCODE, TH.F_FORMAT AS FMTCODE, TH.F_SUBFORMAT AS SFMTCODE, TH.F_LANGUAGE AS LANGCODE, CONVERT(varchar, TH.F_PUBLISHED_DATE, 120) AS PDATE, CONVERT(varchar, TH.F_DATE_REVISED, 120) AS RDATE, TH.F_CUSTOM1 AS cus1, TH.F_CUSTOM2 AS cus2, CASE WHEN NOT (TDF.F_FILE_NAME IS NULL OR TDF.F_FILE_NAME = '') THEN 'SDS - ' + (SELECT F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) + ' - ' + REVERSE(LEFT(REVERSE(TDF.F_FILE_NAME), CHARINDEX('.', REVERSE(TDF.F_FILE_NAME)) - 1)) ELSE '' END AS DOC, 'COVER - ' + (SELECT F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TH.F_LANGUAGE) + ' - HTML' AS COVER, CASE WHEN EXISTS (SELECT 1 FROM T_DOCUMENTS TD WHERE TD.F_ALIAS = TH.F_PRODUCT AND TD.F_LANGUAGE = TH.F_LANGUAGE) THEN 'Related Document' ELSE '' END AS RDOC, CAST(F_GUID AS varchar(36)) + '_HTML' AS [GUID], 'HTML' AS SDS FROM HTML_MSDS TH LEFT JOIN T_DW_FILES TDF ON TH.F_DOC_PATH = CONVERT(varchar(10), TDF.F_RECORD_ID) WHERE (@PRODUCTID IS NULL OR TH.F_PRODUCT LIKE REPLACE(REPLACE(@PRODUCTID, '[', '\['), '_', '\_')ESCAPE '\') AND (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TH.F_PRODUCT_NAME, 'â„¢', '|TM'), 'Â®', '|TS'), 'Â©', '|CP'), 'Â°', '|DEG') LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME, '[', '\['), '_', '\_'), 'â„¢', '|TM'), 'Â®', '|TS'), 'Â©', '|CP'), 'Â°', '|DEG')ESCAPE '\') AND (@FORMAT IS NULL OR TH.F_FORMAT = @FORMAT) AND (@SUBFORMAT IS NULL OR TH.F_SUBFORMAT = @SUBFORMAT OR @SUBFORMAT = '-1') AND (TH.F_SUBFORMAT IN (SELECT MS.F_MSDSTYPE FROM T_MSDSTYPES MS WHERE NOT (MS.F_MSDSTYPE_DESC = '' OR MS.F_MSDSTYPE_DESC IS NULL) AND MS.F_FORMAT = TH.F_FORMAT)) AND (@LANGUAGE IS NULL OR TH.F_LANGUAGE = @LANGUAGE) AND (@PLANT IS NULL OR TH.F_PLANT = @PLANT) AND (@SUPPLIER IS NULL OR TH.F_SUPPLIER LIKE @SUPPLIER) AND (@CASNUM IS NULL OR TH.F_CAS_NUMBERS LIKE @CASNUM) AND (@COMPID IS NULL OR TH.F_COMPONENT_IDS LIKE @COMPID) AND (@KEYWORD IS NULL OR TH.F_KEYWORDS LIKE @KEYWORD) AND (@PUBLISHEDDATE_START IS NULL OR TH.F_PUBLISHED_DATE BETWEEN @PUBLISHEDDATE_START AND @PUBLISHEDDATE_END) AND (@REVISEDDATE_START IS NULL OR TH.F_DATE_REVISED BETWEEN @REVISEDDATE_START AND @REVISEDDATE_END) AND (@ISSUEDATE_START IS NULL OR TH.F_ISSUE_DATE BETWEEN @ISSUEDATE_START AND @ISSUEDATE_END) AND (@DISPOSALDATE_START IS NULL OR TH.F_DISPOSAL_DATE BETWEEN @DISPOSALDATE_START AND @DISPOSALDATE_END) AND (@CUSTOM1 IS NULL OR TH.F_CUSTOM1 LIKE @CUSTOM1) AND (@CUSTOM2 IS NULL OR TH.F_CUSTOM2 LIKE @CUSTOM2) AND (@CUSTOM3 IS NULL OR TH.F_CUSTOM3 LIKE @CUSTOM3) AND (@CUSTOM4 IS NULL OR TH.F_CUSTOM4 LIKE @CUSTOM4) AND (@CUSTOM5 IS NULL OR TH.F_CUSTOM5 LIKE @CUSTOM5) AND TH.F_AUTHORIZED <> 0;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 6

jkramprakash - Monday, August 20, 2018 10:47 AM
Again i am posting with proper alignment.Now i used OPTION (OPTIMIZE FOR UNKNOWN) in my first part at the end of query.Is it correct?.Whether i have to use in all the queries at the end?

You need to find out which queries inside your stored procedure are taking the time. You can just put OPTION (OPTIMIZE FOR UNKNOWN) after individual queries
e.g
SELECT * FROM myTable WHERE col1 = @Value OPTION (OPTIMIZE FOR UNKNOWN) ;

SELECT * FROM myTable2 WHERE col2 = @Value2;
Another thing to try is recompiling the stored procedure on each call, you can do this with:

ALTER PROCEDURE [dbo].[SEARCH_SDS] @PRODUCTID NVARCHAR(50), @PRODUCTNAME NVARCHAR(2000), ... WITH RECOMPILE AS --START - PDF table SELECT DISTINCT TP.F_PRODUCT AS ID, ..rest of store procedure here..
This will recompile the stored procedure each time it is called. If it's called infrequently and doesn't take long to compile it might be worth trying that.

jkramprakash SSCommitted Points: 1870 More actions · Answer 7

I tried to use this below command in end of my first query before Union All operator.But it is showing error message. my first query only taking more time.How can i use this command in my first query before union all operator?

OPTION(OPTIMIZE FOR UNKNOWN)
Union All
--START - RTF table

drew.allen SSC Guru Points: 76986 More actions · Answer 8

jkramprakash - Tuesday, August 21, 2018 10:56 AM
I tried to use this below command in end of my first query before Union All operator.But it is showing error message. my first query only taking more time.How can i use this command in my first query before union all operator?
OPTION(OPTIMIZE FOR UNKNOWN)
Union All
--START - RTF table

You can't. Query options apply to the whole query and must occur at the end of the query. I don't believe that it will cause any problems with the rest of your query.

You could split up your query and save the results to a temp table, if you really only want it to apply to the first part of the query.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA