optimization of stored procedure

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  August 28, 2018 at 9:42 am

  #408318

  Below stored procedure taking more time to complete the process.This procedure contains left join with five view.View names are V_PROD_ALIAS_SYN, V_PROD_ALIAS_SITE, V_PROD_ALIAS_ICO, V_PROD_ALIAS_MANU, V_PROD_ALIAS_CASN).
  All these views contains union all operator.So i am not able to create index on views also.So how can i optimize this stored procedure.Please help.

  CREATE PROCEDURE [dbo].[SEARCH]
  @LANGUAGE NVARCHAR(2),
  @SUBFORMAT NVARCHAR(50),
  @PRODUCTNAME NVARCHAR(200),
  @CASNUM NVARCHAR(200),
  @SITE NVARCHAR(200),
  @ico NVARCHAR(200),
  @manu-2 NVARCHAR(200),
  @GUID NVARCHAR(200)
AS
DECLARE @ALLSUBFORMATS VARCHAR(MAX)
SELECT @ALLSUBFORMATS = COALESCE(@ALLSUBFORMATS + ', ','') + F_VALUE FROM T_LOOKUP WHERE F_PARENT = 'WEBVIEWER_INT_SUB'

    --START - PDF table
  SELECT DISTINCT TP.F_PRODUCT AS ID,
   TP.F_PRODUCT_NAME AS [NAME],
   (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_LANGUAGE AS LANGCODE,
   TP.F_SUBFORMAT AS SFMTCODE,
   TP.F_PLANT AS PLANTCODE,
   TP.F_FORMAT AS FMTCODE,
   CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
   CASE
    WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
   END AS CASNUM ,
   --REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') AS CASNUM,
   TP.F_CUSTOM1 AS cus1,
   TP.F_CUSTOM2 AS cus2,
   --TP.F_CUSTOM5 AS cus5,
   (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
   MAN.F_PHRASE AS MANU,
   CASE
    WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
   END + ' ' +
   CASE
    WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
   END AS SYN,
   IC.F_DATA AS ICO,
   'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
   '' AS COVER,
   CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
   'PDF' AS SDS
  FROM T_PDF_MSDS TP
  LEFT JOIN V_PROD_ALIAS_SYN SYN
   ON TP.F_PRODUCT = SYN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_SITE SIT
   ON TP.F_PRODUCT = SIT.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_ICO IC
   ON TP.F_PRODUCT = IC.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_MANU MAN
   ON TP.F_PRODUCT = MAN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_CASN CASN
   ON TP.F_PRODUCT = CASN.F_PRODUCT
  WHERE
   (@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 '\'
    OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)
   AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))
   AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))
   AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
   AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
   AND (@SITE IS NULL OR SIT.F_TEXT_CODE LIKE @SITE)
   AND (@ICO IS NULL OR IC.F_DATA LIKE @ico)
   AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')
   AND TP.F_AUTHORIZED IN (1,3)

  UNION ALL

  --START - HTML table
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_HTML_MSDS TP
    LEFT JOIN T_DW_FILES TDF
         ON TP.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@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 '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)
        AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))
        AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))
        AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
        ---AND (@CASNUM IS NULL OR CASN.F_DATA LIKE @CASNUM)
        AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
        AND (@SITE IS NULL OR SIT.F_TEXT_CODE LIKE @SITE)
        AND (@ICO IS NULL OR IC.F_DATA LIKE @ico)
        AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
        AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
        AND TP.F_AUTHORIZED IN (1,3);
    ;

  
  

• sgmunson

  SSC Guru

  Points: 110640

  More actions

  August 28, 2018 at 10:16 am

  #2003641

  Views with UNION ALL in them and large row counts are always going to be problematic.   If you can't deconstruct the views within your stored procedure because your company insists on you using the views directly, you may have no way to make it any faster.   If you can deconstruct the views, you may be able to subject each piece to some pre-filtering and dump into a temp table to allow for a final query against all the partial pieces...   but there's no guarantee that such will necessarily be any faster.   You'd have to test it...

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)

• Lowell

  SSC Guru

  Points: 323518

  More actions

  August 28, 2018 at 10:17 am

  #2003642

  your WHERE statements are using functions on a columnName, LIKE statements and OR's, which eliminates the ability to use indexes, so all tables have to be scanned.
  you are also joining against views, so you are probably including tables you don't need as well, or event he same table multiple times. i would replace the views with the real tables,and then eliminate joined tables that are not needed.

  you might be able to rewrite this to build a command and use dynamic SQL for the queries, but you have got to eliminate functions on a column name, and the OR statements.
  can you create a persisted calculated column on TP.F_Product_name that does all the replaces? then you could add an index on it
   WHERE
          (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')

  ---

  --replace this up at the top of the procedure  for cleaner code.
              LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'™','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'

  ---

  these are three different queries really. maybe insert them into a temp table  seperately.
              OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)

  ---

  this could be eliminated with dynamic sql to build the query, since you would know if it is null or not.
  is the function dfnSplit a schema bound inline table value function or multi statement? change it to be the former, or insert those results into a temp table if possible.
          AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))

  ---

  is the function dfnSplit a schema bound inline table value function or multi statement? change it to be the former, or insert those results into a temp table if possible.
          AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))

  ---

  this could be eliminated with dynamic sql to build the query, since you would know if it is null or not.

          AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  August 28, 2018 at 10:18 am

  #2003643

  One quick thing to try is to add at the end of the SQL statement " OPTION (OPTIMIZE FOR UNKNOWN)"
  So the last lines of your SP would look like:
    AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
   AND TP.F_AUTHORIZED IN (1,3)
  OPTION (OPTIMIZE FOR UNKNOWN);

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  August 28, 2018 at 10:30 am

  #2003647

  jkramprakash - Tuesday, August 28, 2018 9:42 AM

  Below stored procedure taking more time to complete the process.This procedure contains left join with five view.View names are V_PROD_ALIAS_SYN, V_PROD_ALIAS_SITE, V_PROD_ALIAS_ICO, V_PROD_ALIAS_MANU, V_PROD_ALIAS_CASN).
  All these views contains union all operator.So i am not able to create index on views also.So how can i optimize this stored procedure.Please help.

  CREATE PROCEDURE [dbo].[SEARCH]
  @LANGUAGE NVARCHAR(2),
  @SUBFORMAT NVARCHAR(50),
  @PRODUCTNAME NVARCHAR(200),
  @CASNUM NVARCHAR(200),
  @SITE NVARCHAR(200),
  @ico NVARCHAR(200),
  @manu-2 NVARCHAR(200),
  @GUID NVARCHAR(200)
AS
DECLARE @ALLSUBFORMATS VARCHAR(MAX)
SELECT @ALLSUBFORMATS = COALESCE(@ALLSUBFORMATS + ', ','') + F_VALUE FROM T_LOOKUP WHERE F_PARENT = 'WEBVIEWER_INT_SUB'

    --START - PDF table
  SELECT DISTINCT TP.F_PRODUCT AS ID,
   TP.F_PRODUCT_NAME AS [NAME],
   (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_LANGUAGE AS LANGCODE,
   TP.F_SUBFORMAT AS SFMTCODE,
   TP.F_PLANT AS PLANTCODE,
   TP.F_FORMAT AS FMTCODE,
   CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
   CASE
    WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
   END AS CASNUM ,
   --REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') AS CASNUM,
   TP.F_CUSTOM1 AS cus1,
   TP.F_CUSTOM2 AS cus2,
   --TP.F_CUSTOM5 AS cus5,
   (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
   MAN.F_PHRASE AS MANU,
   CASE
    WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
   END + ' ' +
   CASE
    WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
   END AS SYN,
   IC.F_DATA AS ICO,
   'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
   '' AS COVER,
   CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
   'PDF' AS SDS
  FROM T_PDF_MSDS TP
  LEFT JOIN V_PROD_ALIAS_SYN SYN
   ON TP.F_PRODUCT = SYN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_SITE SIT
   ON TP.F_PRODUCT = SIT.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_ICO IC
   ON TP.F_PRODUCT = IC.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_MANU MAN
   ON TP.F_PRODUCT = MAN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_CASN CASN
   ON TP.F_PRODUCT = CASN.F_PRODUCT
  WHERE
   (@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 '\'
    OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)
   AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))
   AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))
   AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
   AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
   AND (@SITE IS NULL OR SIT.F_TEXT_CODE LIKE @SITE)
   AND (@ICO IS NULL OR IC.F_DATA LIKE @ico)
   AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')
   AND TP.F_AUTHORIZED IN (1,3)

  UNION ALL

  --START - HTML table
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_HTML_MSDS TP
    LEFT JOIN T_DW_FILES TDF
         ON TP.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@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 '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)
        AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))
        AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))
        AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
        ---AND (@CASNUM IS NULL OR CASN.F_DATA LIKE @CASNUM)
        AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
        AND (@SITE IS NULL OR SIT.F_TEXT_CODE LIKE @SITE)
        AND (@ICO IS NULL OR IC.F_DATA LIKE @ico)
        AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
        AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
        AND TP.F_AUTHORIZED IN (1,3);
    ;

  
  

  There are multiple problems.
  You have table functions that I'm guessing that are multi-statement functions instead of in-line. That's a problem maker. Try the DelimitedSplit8k shown on this article: Tally OH! An Improved SQL 8K “CSV Splitter†Function - SQLServerCentral
  You also have a catch-all query which is typical for search  procedures. That's one of the issues explained by Gail Shaw in this article, along with the solutions: How To Confuse The SQL Server Query Optimizer
  You also have multiple non-sargable expressions that could be eliminated by using an indexed computed column
  One more thing is that you could remove the DISTINCT and use UNION instead of UNION ALL. That's the option that might give the less improvement, but any penny might count.
  Last option is to redesign everything from scratch. But I'm sure you won't want to do it.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  August 28, 2018 at 11:33 am

  #2003667

  Lowell - Tuesday, August 28, 2018 10:17 AM

  your WHERE statements are using functions on a columnName, LIKE statements and OR's, which eliminates the ability to use indexes, so all tables have to be scanned.
  you are also joining against views, so you are probably including tables you don't need as well, or event he same table multiple times. i would replace the views with the real tables,and then eliminate joined tables that are not needed.

  you might be able to rewrite this to build a command and use dynamic SQL for the queries, but you have got to eliminate functions on a column name, and the OR statements.
  can you create a persisted calculated column on TP.F_Product_name that does all the replaces? then you could add an index on it
   WHERE
          (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')

  ---

  --replace this up at the top of the procedure  for cleaner code.
              LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'

  ---

  these are three different queries really. maybe insert them into a temp table  seperately.
              OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)

  ---

  this could be eliminated with dynamic sql to build the query, since you would know if it is null or not.
  is the function dfnSplit a schema bound inline table value function or multi statement? change it to be the former, or insert those results into a temp table if possible.
          AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))

  ---

  is the function dfnSplit a schema bound inline table value function or multi statement? change it to be the former, or insert those results into a temp table if possible.
          AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))

  ---

  this could be eliminated with dynamic sql to build the query, since you would know if it is null or not.

          AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')

  how can i insert this part into temp table and  how shall i  use the temp table with main query?.Please give query example because
  SYN.F_DATA LIKE is coming from view(V_PROD_ALIAS_SYN)

  .

  OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  August 29, 2018 at 1:39 am

  #2003766

  Jonathan AC Roberts - Tuesday, August 28, 2018 10:18 AM

  One quick thing to try is to add at the end of the SQL statement " OPTION (OPTIMIZE FOR UNKNOWN)"
  So the last lines of your SP would look like:
    AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
   AND TP.F_AUTHORIZED IN (1,3)
  OPTION (OPTIMIZE FOR UNKNOWN);

  now i combined two select statements in union all operation in one select statement and i used option(optimize for unknow) in last.is it correct?will it improve performance?

  SELECT ID,NAME,SFMT,LANG,LANGCODE,SFMTCODE,PLANTCODE,FMTCODE,RDATE,CASNUM,cus1,cus2,cus5,MANU,SYN,ICO,DOC,COVER,GUID,SDS FROM (
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
        '' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
        'PDF' AS SDS
    FROM T_PDF_MSDS TP
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')
        AND TP.F_AUTHORIZED IN (1,3)
        
        UNION ALL
    
    --START HTML CODE
    
    --INSERT INTO @MSDS
     SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_HTML_MSDS TP
    LEFT JOIN T_DW_FILES TDF
         ON TP.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        ---AND (@CASNUM IS NULL OR CASN.F_DATA LIKE @CASNUM)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
        AND TP.F_AUTHORIZED IN (1,3))v
        OPTION (OPTIMIZE FOR UNKNOWN);
    

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  August 29, 2018 at 2:46 am

  #2003772

  jkramprakash - Wednesday, August 29, 2018 1:39 AM

  Jonathan AC Roberts - Tuesday, August 28, 2018 10:18 AM

  One quick thing to try is to add at the end of the SQL statement " OPTION (OPTIMIZE FOR UNKNOWN)"
  So the last lines of your SP would look like:
    AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
   AND TP.F_AUTHORIZED IN (1,3)
  OPTION (OPTIMIZE FOR UNKNOWN);

  now i combined two select statements in union all operation in one select statement and i used option(optimize for unknow) in last.is it correct?will it improve performance?

  SELECT ID,NAME,SFMT,LANG,LANGCODE,SFMTCODE,PLANTCODE,FMTCODE,RDATE,CASNUM,cus1,cus2,cus5,MANU,SYN,ICO,DOC,COVER,GUID,SDS FROM (
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
        '' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
        'PDF' AS SDS
    FROM T_PDF_MSDS TP
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')
        AND TP.F_AUTHORIZED IN (1,3)
        
        UNION ALL
    
    --START HTML CODE
    
    --INSERT INTO @MSDS
     SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_HTML_MSDS TP
    LEFT JOIN T_DW_FILES TDF
         ON TP.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        ---AND (@CASNUM IS NULL OR CASN.F_DATA LIKE @CASNUM)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
        AND TP.F_AUTHORIZED IN (1,3))v
        OPTION (OPTIMIZE FOR UNKNOWN);
    

  That hint is good for queries like this, so it probably will help.
  It's easy enough to see if it improves performance, just run it with the hint and run it without the hint on a few different calls and see for yourself.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  August 29, 2018 at 11:07 am

  #2003850

  Luis Cazares - Tuesday, August 28, 2018 10:30 AM

  jkramprakash - Tuesday, August 28, 2018 9:42 AM

  Below stored procedure taking more time to complete the process.This procedure contains left join with five view.View names are V_PROD_ALIAS_SYN, V_PROD_ALIAS_SITE, V_PROD_ALIAS_ICO, V_PROD_ALIAS_MANU, V_PROD_ALIAS_CASN).
  All these views contains union all operator.So i am not able to create index on views also.So how can i optimize this stored procedure.Please help.

  CREATE PROCEDURE [dbo].[SEARCH]
  @LANGUAGE NVARCHAR(2),
  @SUBFORMAT NVARCHAR(50),
  @PRODUCTNAME NVARCHAR(200),
  @CASNUM NVARCHAR(200),
  @SITE NVARCHAR(200),
  @ico NVARCHAR(200),
  @manu-2 NVARCHAR(200),
  @GUID NVARCHAR(200)
AS
DECLARE @ALLSUBFORMATS VARCHAR(MAX)
SELECT @ALLSUBFORMATS = COALESCE(@ALLSUBFORMATS + ', ','') + F_VALUE FROM T_LOOKUP WHERE F_PARENT = 'WEBVIEWER_INT_SUB'

    --START - PDF table
  SELECT DISTINCT TP.F_PRODUCT AS ID,
   TP.F_PRODUCT_NAME AS [NAME],
   (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_LANGUAGE AS LANGCODE,
   TP.F_SUBFORMAT AS SFMTCODE,
   TP.F_PLANT AS PLANTCODE,
   TP.F_FORMAT AS FMTCODE,
   CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
   CASE
    WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
   END AS CASNUM ,
   --REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') AS CASNUM,
   TP.F_CUSTOM1 AS cus1,
   TP.F_CUSTOM2 AS cus2,
   --TP.F_CUSTOM5 AS cus5,
   (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
   MAN.F_PHRASE AS MANU,
   CASE
    WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
   END + ' ' +
   CASE
    WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
   END AS SYN,
   IC.F_DATA AS ICO,
   'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
   '' AS COVER,
   CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
   'PDF' AS SDS
  FROM T_PDF_MSDS TP
  LEFT JOIN V_PROD_ALIAS_SYN SYN
   ON TP.F_PRODUCT = SYN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_SITE SIT
   ON TP.F_PRODUCT = SIT.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_ICO IC
   ON TP.F_PRODUCT = IC.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_MANU MAN
   ON TP.F_PRODUCT = MAN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_CASN CASN
   ON TP.F_PRODUCT = CASN.F_PRODUCT
  WHERE
   (@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 '\'
    OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)
   AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))
   AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))
   AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
   AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
   AND (@SITE IS NULL OR SIT.F_TEXT_CODE LIKE @SITE)
   AND (@ICO IS NULL OR IC.F_DATA LIKE @ico)
   AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')
   AND TP.F_AUTHORIZED IN (1,3)

  UNION ALL

  --START - HTML table
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_HTML_MSDS TP
    LEFT JOIN T_DW_FILES TDF
         ON TP.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@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 '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)
        AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))
        AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))
        AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
        ---AND (@CASNUM IS NULL OR CASN.F_DATA LIKE @CASNUM)
        AND (@CASNUM IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM)
        AND (@SITE IS NULL OR SIT.F_TEXT_CODE LIKE @SITE)
        AND (@ICO IS NULL OR IC.F_DATA LIKE @ico)
        AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
        AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
        AND TP.F_AUTHORIZED IN (1,3);
    ;

  
  

  There are multiple problems.
  You have table functions that I'm guessing that are multi-statement functions instead of in-line. That's a problem maker. Try the DelimitedSplit8k shown on this article: Tally OH! An Improved SQL 8K “CSV Splitter†Function - SQLServerCentral
  You also have a catch-all query which is typical for search  procedures. That's one of the issues explained by Gail Shaw in this article, along with the solutions: How To Confuse The SQL Server Query Optimizer
  You also have multiple non-sargable expressions that could be eliminated by using an indexed computed column
  One more thing is that you could remove the DISTINCT and use UNION instead of UNION ALL. That's the option that might give the less improvement, but any penny might count.
  Last option is to redesign everything from scratch. But I'm sure you won't want to do it.

  shall i convert this below sub query in the two main query into left join in the from clause?.Is it right idea?already five left join  is there.
  it improve performance?
  SUB QUERY
  
(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,

  After conversion.like this for example
  SELECT DISTINCT TP.F_PRODUCT AS ID,
   TP.F_PRODUCT_NAME AS [NAME],
   MS.F_MSDSTYPE_DESC  AS SFMT,
   TL.F_LANGUAGE_NAME AS LANG,
    TP.F_LANGUAGE AS LANGCODE,
     TP.F_SUBFORMAT AS SFMTCODE,
      TP.F_PLANT AS PLANTCODE,
      TP.F_FORMAT AS FMTCODE,
       CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_PDF_MSDS TP
 LEFT JOIN T_MSDSTYPES MS
 ON MS.F_MSDSTYPE = TP.F_SUBFORMAT
 AND MS.F_FORMAT = TP.F_FORMAT
LEFT JOIN T_LANGUAGES TL 
ON TL.F_LANGUAGE = TP.F_LANGUAGE
LEFT JOIN V_PROD_ALIAS_SYN SYN
   ON TP.F_PRODUCT = SYN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_SITE SIT
   ON TP.F_PRODUCT = SIT.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_ICO IC
   ON TP.F_PRODUCT = IC.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_MANU MAN
   ON TP.F_PRODUCT = MAN.F_PRODUCT
  LEFT JOIN V_PROD_ALIAS_CASN CASN
   ON TP.F_PRODUCT = CASN.F_PRODUCT
  WHERE clause
  ................
  
  shall i convert like this in  two main query in FROM clause of join?

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  August 29, 2018 at 8:10 pm

  #2003949

  Lowell - Tuesday, August 28, 2018 10:17 AM

  your WHERE statements are using functions on a columnName, LIKE statements and OR's, which eliminates the ability to use indexes, so all tables have to be scanned.
  you are also joining against views, so you are probably including tables you don't need as well, or event he same table multiple times. i would replace the views with the real tables,and then eliminate joined tables that are not needed.

  you might be able to rewrite this to build a command and use dynamic SQL for the queries, but you have got to eliminate functions on a column name, and the OR statements.
  can you create a persisted calculated column on TP.F_Product_name that does all the replaces? then you could add an index on it
   WHERE
          (@PRODUCTNAME IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')

  ---

  --replace this up at the top of the procedure  for cleaner code.
              LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'

  ---

  these are three different queries really. maybe insert them into a temp table  seperately.
              OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME OR SYN.F_DATA LIKE @PRODUCTNAME)

  ---

  this could be eliminated with dynamic sql to build the query, since you would know if it is null or not.
  is the function dfnSplit a schema bound inline table value function or multi statement? change it to be the former, or insert those results into a temp table if possible.
          AND (@SUBFORMAT IS NULL OR @SUBFORMAT = '-1' OR TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@SUBFORMAT,',')))

  ---

  is the function dfnSplit a schema bound inline table value function or multi statement? change it to be the former, or insert those results into a temp table if possible.
          AND (TP.F_SUBFORMAT IN (SELECT VAL FROM dfnSplit(@ALLSUBFORMATS,',')))

  ---

  this could be eliminated with dynamic sql to build the query, since you would know if it is null or not.

          AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')

  shall i convert this below sub query in the main query into left join in the from clause?.Is it right idea?already five left join is there.is it improve performance?
  Sub Query

  (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,

  Like this

  SELECT DISTINCT TP.F_PRODUCT AS ID,
 TP.F_PRODUCT_NAME AS [NAME],
 MS.F_MSDSTYPE_DESC AS SFMT,
 TL.F_LANGUAGE_NAME AS LANG,
  TP.F_LANGUAGE AS LANGCODE,
  TP.F_SUBFORMAT AS SFMTCODE,
  TP.F_PLANT AS PLANTCODE,
  TP.F_FORMAT AS FMTCODE,
   CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
   CASE
    WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
   END AS CASNUM ,
   --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
   TP.F_CUSTOM1 AS cus1,
   TP.F_CUSTOM2 AS cus2,
   --TP.F_CUSTOM5 AS cus5,
   (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
   MAN.F_PHRASE AS MANU,
   CASE
    WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
   END + ' ' +
   CASE
    WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
   END AS SYN,
   IC.F_DATA AS ICO,
   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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
   CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
   'HTML' AS SDS
  FROM T_PDF_MSDS TP
LEFT JOIN T_MSDSTYPES MS
ON MS.F_MSDSTYPE = TP.F_SUBFORMAT
AND MS.F_FORMAT = TP.F_FORMAT
LEFT JOIN T_LANGUAGES TL
ON TL.F_LANGUAGE = TP.F_LANGUAGE
LEFT JOIN V_PROD_ALIAS_SYN SYN
 ON TP.F_PRODUCT = SYN.F_PRODUCT
LEFT JOIN V_PROD_ALIAS_SITE SIT
 ON TP.F_PRODUCT = SIT.F_PRODUCT
LEFT JOIN V_PROD_ALIAS_ICO IC
 ON TP.F_PRODUCT = IC.F_PRODUCT
LEFT JOIN V_PROD_ALIAS_MANU MAN
 ON TP.F_PRODUCT = MAN.F_PRODUCT
LEFT JOIN V_PROD_ALIAS_CASN CASN
 ON TP.F_PRODUCT = CASN.F_PRODUCT
WHERE clause-----

  is it right method to convert sub query into left join like above(highlighted bold) in the sceanerio?.

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  August 31, 2018 at 3:58 am

  #2004111

  Jonathan AC Roberts - Wednesday, August 29, 2018 2:46 AM

  jkramprakash - Wednesday, August 29, 2018 1:39 AM

  Jonathan AC Roberts - Tuesday, August 28, 2018 10:18 AM

  One quick thing to try is to add at the end of the SQL statement " OPTION (OPTIMIZE FOR UNKNOWN)"
  So the last lines of your SP would look like:
    AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
   AND TP.F_AUTHORIZED IN (1,3)
  OPTION (OPTIMIZE FOR UNKNOWN);

  now i combined two select statements in union all operation in one select statement and i used option(optimize for unknow) in last.is it correct?will it improve performance?

  SELECT ID,NAME,SFMT,LANG,LANGCODE,SFMTCODE,PLANTCODE,FMTCODE,RDATE,CASNUM,cus1,cus2,cus5,MANU,SYN,ICO,DOC,COVER,GUID,SDS FROM (
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
        '' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
        'PDF' AS SDS
    FROM T_PDF_MSDS TP
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')
        AND TP.F_AUTHORIZED IN (1,3)
        
        UNION ALL
    
    --START HTML CODE
    
    --INSERT INTO @MSDS
     SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_HTML_MSDS TP
    LEFT JOIN T_DW_FILES TDF
         ON TP.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        ---AND (@CASNUM IS NULL OR CASN.F_DATA LIKE @CASNUM)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
        AND TP.F_AUTHORIZED IN (1,3))v
        OPTION (OPTIMIZE FOR UNKNOWN);
    

  That hint is good for queries like this, so it probably will help.
  It's easy enough to see if it improves performance, just run it with the hint and run it without the hint on a few different calls and see for yourself.

  Assigning procedure parameter values into local variable is good one?will it improve further?

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  August 31, 2018 at 5:24 am

  #2004117

  jkramprakash - Friday, August 31, 2018 3:58 AM

  Jonathan AC Roberts - Wednesday, August 29, 2018 2:46 AM

  jkramprakash - Wednesday, August 29, 2018 1:39 AM

  Jonathan AC Roberts - Tuesday, August 28, 2018 10:18 AM

  One quick thing to try is to add at the end of the SQL statement " OPTION (OPTIMIZE FOR UNKNOWN)"
  So the last lines of your SP would look like:
    AND (@MANU IS NULL OR MAN.F_TEXT_CODE LIKE @manu-2)
   AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
   AND TP.F_AUTHORIZED IN (1,3)
  OPTION (OPTIMIZE FOR UNKNOWN);

  now i combined two select statements in union all operation in one select statement and i used option(optimize for unknow) in last.is it correct?will it improve performance?

  SELECT ID,NAME,SFMT,LANG,LANGCODE,SFMTCODE,PLANTCODE,FMTCODE,RDATE,CASNUM,cus1,cus2,cus5,MANU,SYN,ICO,DOC,COVER,GUID,SDS FROM (
    SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_DATE_REVISED,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        'SDS - ' + (SELECT TL.F_LANGUAGE_NAME FROM T_LANGUAGES TL WHERE TL.F_LANGUAGE = TP.F_LANGUAGE) + ' - PDF' AS DOC,
        '' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_PDF' AS [GUID],
        'PDF' AS SDS
    FROM T_PDF_MSDS TP
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_CUSTOM5 IS NULL OR TP.F_CUSTOM5 = '')
        AND TP.F_AUTHORIZED IN (1,3)
        
        UNION ALL
    
    --START HTML CODE
    
    --INSERT INTO @MSDS
     SELECT DISTINCT TP.F_PRODUCT AS ID,
        TP.F_PRODUCT_NAME AS [NAME],
        (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_LANGUAGE AS LANGCODE,
        TP.F_SUBFORMAT AS SFMTCODE,
        TP.F_PLANT AS PLANTCODE,
        TP.F_FORMAT AS FMTCODE,
        CONVERT(VARCHAR,TP.F_ISSUE_DATE,120) AS RDATE,
        CASE
            WHEN NOT(REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') = '') THEN REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') ELSE REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ')
        END AS CASNUM ,
        --REPLACE(REPLACE(LTRIM(RTRIM(CASN.F_DATA)),'¿',' '),'',' ') AS CASNUM,
        TP.F_CUSTOM1 AS cus1,
        TP.F_CUSTOM2 AS cus2,
        --TP.F_CUSTOM5 AS cus5,
        (SELECT TC.F_COUNTRY_NAME FROM T_COUNTRIES TC WHERE TC.F_COUNTRY_CODE = TP.F_CUSTOM5) AS cus5,
        MAN.F_PHRASE AS MANU,
        CASE
            WHEN NOT(TP.F_CUSTOM3 IS NULL OR TP.F_CUSTOM3 = '') THEN TP.F_CUSTOM3 ELSE ''
        END + ' ' +
        CASE
            WHEN NOT(SYN.F_DATA IS NULL OR SYN.F_DATA = '') THEN SYN.F_DATA ELSE ''
        END AS SYN,
        IC.F_DATA AS ICO,
        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 = TP.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 = TP.F_LANGUAGE) + ' - HTML' AS COVER,
        CAST(TP.F_GUID AS VARCHAR(36)) + '_HTML' AS [GUID],
        'HTML' AS SDS
    FROM T_HTML_MSDS TP
    LEFT JOIN T_DW_FILES TDF
         ON TP.F_DOC_PATH = CONVERT(VARCHAR(10),TDF.F_RECORD_ID)
    LEFT JOIN V_PROD_ALIAS_SYN SYN
        ON TP.F_PRODUCT = SYN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_SITE SIT
        ON TP.F_PRODUCT = SIT.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_ICO IC
        ON TP.F_PRODUCT = IC.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_MANU MAN
        ON TP.F_PRODUCT = MAN.F_PRODUCT
    LEFT JOIN V_PROD_ALIAS_CASN CASN
        ON TP.F_PRODUCT = CASN.F_PRODUCT
    WHERE
        (@PRODUCTNAME1 IS NULL OR REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME,'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG')
            LIKE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@PRODUCTNAME1,'[','\['),'_','\_'),'â„¢','|TM'),'®','|TS'),'©','|CP'),'°','|DEG') ESCAPE '\'
            OR REPLACE(TP.F_CUSTOM1,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM2,'^','') LIKE @PRODUCTNAME1 OR REPLACE(TP.F_CUSTOM3,'^','') LIKE @PRODUCTNAME1 OR SYN.F_DATA LIKE @PRODUCTNAME1)
        AND (@SUBFORMAT1 IS NULL OR TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (TP.F_SUBFORMAT IN (SELECT SUBFORMAT FROM @TEMP))
        AND (@LANGUAGE1 IS NULL OR TP.F_LANGUAGE = @LANGUAGE1)
        ---AND (@CASNUM IS NULL OR CASN.F_DATA LIKE @CASNUM)
        AND (@CASNUM1 IS NULL OR REPLACE(LTRIM(RTRIM(REPLACE(CASN.F_DATA,'¿',' '))),' ','; ') LIKE @CASNUM1 OR REPLACE(LTRIM(RTRIM(REPLACE(TP.F_CAS_NUMBERS,'¿',' '))),' ','; ') LIKE @CASNUM1)
        AND (@SITE1 IS NULL OR SIT.F_TEXT_CODE LIKE @SITE1)
        AND (@ICO1 IS NULL OR IC.F_DATA LIKE @ICO1)
        AND (@MANU1 IS NULL OR MAN.F_TEXT_CODE LIKE @MANU1)
        AND (TP.F_DOC_PATH IN (SELECT CONVERT(VARCHAR(10),TDF.F_RECORD_ID) FROM T_DW_FILES TDF))
        AND TP.F_AUTHORIZED IN (1,3))v
        OPTION (OPTIMIZE FOR UNKNOWN);
    

  That hint is good for queries like this, so it probably will help.
  It's easy enough to see if it improves performance, just run it with the hint and run it without the hint on a few different calls and see for yourself.

  Assigning procedure parameter values into local variable is good one?will it improve further?

  Assigning parameters to local variables is one way of stopping parameter sniffing. Parameter sniffing is generally useful but sometimes it causes problems. I you have OPTION (OPTIMIZE FOR UNKNOWN) it won't make any difference whether you assign parameters to local variables or not as it prevents parameter sniffing. My advice is to test different versions of your stored procedure with different calls and find out what works best. You should also be looking at the execution plan and focusing on the operations showing a large percentage.

• ScottPletcher

  SSC Guru

  Points: 101105

  More actions

  August 31, 2018 at 8:19 am

  #2004137

  With that many variables in the query, I'd think you want to use the RECOMPILE option:
  OPTION (RECOMPILE)
  I don't think the OPTIMIZE clause would then be relevant, but you could still specify if you wanted to (unless SQL gives an error about it, of course).

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• Jonathan AC Roberts

  SSCoach

  Points: 18356

  More actions

  August 31, 2018 at 9:15 am

  #2004146

  ScottPletcher - Friday, August 31, 2018 8:19 AM

  With that many variables in the query, I'd think you want to use the RECOMPILE option:
  OPTION (RECOMPILE)
  I don't think the OPTIMIZE clause would then be relevant, but you could still specify if you wanted to (unless SQL gives an error about it, of course).

  Yes, you should use one or the other, not both. jkramprakash really needs to test the SP against production sized data with different optimisations settings  to see which gives the best results.