Post reply

Query Taking more time to Run

  • March 2, 2018 at 7:18 pm

    #403153

    In this stored procedure, the query is taking a lot of time to return the results. Is there any way to make it faster? The tables contains nearly 1 lakshs of records. I mentioned which part taking more time in below query.Please help.

    CREATE PROCEDURE [dbo].[SEARCH]
        @P_NAME VARCHAR(2000),
        @S_FORMAT VARCHAR(4),
        @LANGUAGE VARCHAR(2),
        @C1 VARCHAR(200),
        @C2 VARCHAR(200),
        @PRDGRP VARCHAR(200)
    AS
        -- PDF table
        SELECT
            PM.F_PRODUCT  AS ID,
            PM.F_PRODUCT_NAME AS [NAME],
            --PM.F_PLANT   AS PNT,
            --PM.F_FORMAT   AS FMT,
            --PM.F_SUBFORMAT  AS SFMT,
            --PM.F_LANGUAGE  AS LANG,
            (SELECT F_COMPANY_NAME FROM T_PLANT TPL WHERE TPL.F_PLANT = PM.F_PLANT) AS PNT,
            (SELECT F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = PM.F_FORMAT) AS FMT,
          (SELECT PM.F_SUBFORMAT + ' - ' + F_MSDSTYPE_DESC FROM T_TYPES MS WHERE MS.F_MSDSTYPE = PM.F_SUBFORMAT AND MS.F_FORMAT = PM.F_FORMAT) AS SFMT,
            (SELECT F_LANGUAGE_NAME FROM T_LANG TL WHERE TL.F_LANGUAGE = PM.F_LANGUAGE) AS LANG,
            PM.F_DOC_PATH  AS DPATH,
            CONVERT(VARCHAR,PM.F_PUBLISHED_DATE,120) AS PDATE,
            CONVERT(VARCHAR,PM.F_DATE_REVISED,120) AS RDATE,
            CONVERT(VARCHAR,PM.F_ISSUE_DATE,120)  AS IDATE,
            PM.F_AUTHORIZED    AS AUT,
            PM.F_CUSTOM1     AS cus1,
            PM.F_CUSTOM2     AS cus2,
            /*
            -- ATTENTION: This will drastically slow down the query!
            SUBSTRING(
                (
                    CASE WHEN PM.F_CUSTOM2 IS NULL OR PM.F_CUSTOM2 = '' THEN '' ELSE PM.F_CUSTOM2 + ', ' END
                    +
                    ISNULL(
                        (
                        SELECT TOP 1 F_DATA FROM (
                            SELECT DISTINCT
                                RPDS.F_ALIAS,
                                STUFF((
                                    SELECT ', ' + A.F_DATA
                                    FROM T_REP_PROD A
                                    WHERE
                                        A.F_ALIAS   = RPDS.F_ALIAS
                                        AND A.F_DATA_CODE = RPDS.F_DATA_CODE
                                        AND PM.F_PRODUCT = A.F_ALIAS
                                    FOR XML PATH(''), TYPE
                                ).value( '.', 'NVARCHAR(MAX)' ), 1, 2, '' ) F_DATA
                            FROM T_REP_PROD RPDS
                            INNER JOIN (
                                SELECT DISTINCT F_CUSTOM2 FROM T_TYPES WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2!='')
                            ) MT
                            ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE AND RPDS.F_ALIAS = PM.F_PRODUCT
                        ) A
                        )
                    , '')
                )
            , 1, 200)     AS cus2,
            */
            UPPER(PG.F_PRODUCT_GROUP) AS PGROUP,    
            PGN.F_PROD_GROUP_NAME  AS PGROUPN,    
            PM.F_REV_NUM AS REVNUM,
            PAN.F_ALIAS AS ALIAS,
            'Download PDF' AS DOC,
            CAST(F_GUID AS VARCHAR(36))+'_PDF' AS [GUID]
        FROM
            T_PDF PM
        INNER JOIN
            T_PRODUCT_ALIAS_NAMES PAN
            ON PM.F_PRODUCT = PAN.F_ALIAS
        INNER JOIN
            T_PROD_GROUPING PG
            ON PG.F_PRODUCT = PAN.F_PRODUCT    
        INNER JOIN
            T_PRODUCT_GROUPS PGN
            ON PG.F_PRODUCT_GROUP = PGN.F_PRODUCT_GROUP
        WHERE
            (@P_NAME IS NULL OR PM.F_PRODUCT_NAME LIKE @P_NAME)
            AND (@S_FORMAT IS NULL OR PM.F_SUBFORMAT = @S_FORMAT OR @S_FORMAT = '-1')
            AND (@LANGUAGE IS NULL OR PM.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
            AND (@C1 IS NULL OR PM.F_CUSTOM1 LIKE @C1)
                   AND (
                @C2 IS NULL
                OR PM.F_CUSTOM2 LIKE @CUSTOM2
                OR (
                    @C2 IS NOT NULL
                    AND
                    EXISTS(
                        SELECT 1 FROM (
                            SELECT DISTINCT
                                F_ALIAS,
                                F_DATA_CODE,
                                STUFF((
                                    SELECT ', ' + A.F_DATA
                                    FROM T_REP_PROD A
                                    WHERE
                                        A.F_ALIAS=RPDS.F_ALIAS
                                        AND A.F_DATA_CODE=RPDS.F_DATA_CODE
                                        AND PM.F_PRODUCT = A.F_ALIAS
                                    FOR XML PATH(''), TYPE
                                ).value( '.', 'NVARCHAR(MAX)' ), 1, 2, '' ) F_DATA
                            FROM T_REP_PROD RPDS
                            INNER JOIN (
                                SELECT DISTINCT F_CUSTOM2 FROM T_TYPES WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2!='')
                            ) MT
                            ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE and RPDS.F_ALIAS = PM.F_PRODUCT
                        ) RPDS_CUSTOM2
                        WHERE RPDS_CUSTOM2.F_DATA LIKE @c2
                    )
                )
            )
            AND PM.F_AUTHORIZED != 0
            AND (@PRDGRP IS NULL OR PG.F_PRODUCT_GROUP LIKE @PRDGRP OR @PRDGRP = '-1')
            AND PG.F_PRODUCT_GROUP LIKE '%WV'
        ;
    GO

  • March 3, 2018 at 9:08 am

    #1981852

    https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/

    Specifically the second section

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

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

  • March 4, 2018 at 3:52 am

    #1981877

    AND EXISTS(SELECT 1
        FROM
         (SELECT DISTINCT
           F_ALIAS,
           F_DATA_CODE,
           STUFF((SELECT ', ' + A.F_DATA
             FROM T_REP_DATA A
             WHERE A.F_ALIAS = RPDS.F_ALIAS
              AND A.F_DATA_CODE = RPDS.F_DATA_CODE
              AND '1:WVTST{PROD006' = A.F_ALIAS
             FOR XML PATH(''), TYPE).value( '.', 'NVARCHAR(MAX)'), 1, 2, '') F_DATA
          FROM T_REP_DATA RPDS
          INNER JOIN
           (SELECT DISTINCT F_CUSTOM2
           FROM T_MSDSTYPES
           WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2 != '')) MT ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE and RPDS.F_ALIAS = '1:WVTST{PROD006') RPDS_CUSTOM2)    
        WHERE RPDS_CUSTOM2.F_DATA LIKE '%MANU'

    the above part in our original post only taking more time.please give some suggestion to rewrite the query.

  • March 4, 2018 at 4:30 am

    #1981879

    jkramprakash - Sunday, March 4, 2018 3:52 AM

    AND EXISTS(SELECT 1
        FROM
         (SELECT DISTINCT
           F_ALIAS,
           F_DATA_CODE,
           STUFF((SELECT ', ' + A.F_DATA
             FROM T_REP_DATA A
             WHERE A.F_ALIAS = RPDS.F_ALIAS
              AND A.F_DATA_CODE = RPDS.F_DATA_CODE
              AND '1:WVTST{PROD006' = A.F_ALIAS
             FOR XML PATH(''), TYPE).value( '.', 'NVARCHAR(MAX)'), 1, 2, '') F_DATA
          FROM T_REP_DATA RPDS
          INNER JOIN
           (SELECT DISTINCT F_CUSTOM2
           FROM T_MSDSTYPES
           WHERE (F_CUSTOM2 IS NOT NULL AND F_CUSTOM2 != '')) MT ON MT.F_CUSTOM2 = RPDS.F_DATA_CODE and RPDS.F_ALIAS = '1:WVTST{PROD006') RPDS_CUSTOM2)    
        WHERE RPDS_CUSTOM2.F_DATA LIKE '%MANU'

    the above part in our original post only taking more time.please give some suggestion to rewrite the query.

    Since it's repeated anyway, remove it from the main query and materialize as a temp table. You might have to mess around with it to make it work properly  - not sample data to work with. Personally I wouldn't consider this a final step in tuning the query, there's too much wrong with it. You will have to examine the execution plan of both queries for that.

    CREATE PROCEDURE [dbo].[SEARCH]
     @P_NAME VARCHAR(2000),
     @S_FORMAT VARCHAR(4),
     @LANGUAGE VARCHAR(2),
     @C1 VARCHAR(200),
     @C2 VARCHAR(200),
     @PRDGRP VARCHAR(200)
    AS


    SELECT --DISTINCT
     RPDS.F_ALIAS,
     STUFF((
      SELECT ', ' + a.F_DATA
      FROM T_REP_PROD a
      WHERE a.F_ALIAS = RPDS.F_ALIAS
       AND a.F_DATA_CODE = RPDS.F_DATA_CODE
       AND a.F_ALIAS = PM.F_PRODUCT
      FOR XML PATH(''),TYPE).value( '.', 'NVARCHAR(MAX)'
     ), 1, 2, '' ) F_DATA
    INTO #RPDS_CUSTOM2
    FROM T_REP_PROD RPDS
    WHERE EXISTS (SELECT 1 FROM T_TYPES ti WHERE ti.F_CUSTOM2 = RPDS.F_DATA_CODE)
     AND RPDS.F_ALIAS = PM.F_PRODUCT
    GROUP BY RPDS.F_ALIAS

    -- PDF table
    SELECT
     PM.F_PRODUCT AS ID,
     PM.F_PRODUCT_NAME AS [NAME],
     (SELECT F_COMPANY_NAME FROM T_PLANT TPL WHERE TPL.F_PLANT = PM.F_PLANT) AS PNT,
     (SELECT F_FORMAT_DESC FROM T_FORMATS TF WHERE TF.F_FORMAT = PM.F_FORMAT) AS FMT,
     (SELECT PM.F_SUBFORMAT + ' - ' + F_MSDSTYPE_DESC FROM T_TYPES MS WHERE MS.F_MSDSTYPE = PM.F_SUBFORMAT AND MS.F_FORMAT = PM.F_FORMAT) AS SFMT,
     (SELECT F_LANGUAGE_NAME FROM T_LANG TL WHERE TL.F_LANGUAGE = PM.F_LANGUAGE) AS LANG,
     PM.F_DOC_PATH AS DPATH,
     CONVERT(VARCHAR,PM.F_PUBLISHED_DATE,120) AS PDATE,
     CONVERT(VARCHAR,PM.F_DATE_REVISED,120) AS RDATE,
     CONVERT(VARCHAR,PM.F_ISSUE_DATE,120) AS IDATE,
     PM.F_AUTHORIZED AS AUT,
     PM.F_CUSTOM1 AS cus1,
     PM.F_CUSTOM2 AS cus2,
     -- ATTENTION: This will drastically slow down the query!
     SUBSTRING(
      (
      CASE WHEN PM.F_CUSTOM2 IS NULL OR PM.F_CUSTOM2 = '' THEN '' ELSE PM.F_CUSTOM2 + ', ' END
      +
      ISNULL(
      (
      SELECT TOP 1 F_DATA FROM (
       SELECT RPDS.F_DATA
       FROM #RPDS_CUSTOM2 RPDS 
       WHERE RPDS.F_ALIAS = PM.F_PRODUCT
       ) A
      )
      , '')
      )
      , 1, 200) AS cus2,
     UPPER(PG.F_PRODUCT_GROUP) AS PGROUP,
     PGN.F_PROD_GROUP_NAME AS PGROUPN,
     PM.F_REV_NUM AS REVNUM,
     PAN.F_ALIAS AS ALIAS,
     'Download PDF' AS DOC,
     CAST(F_GUID AS VARCHAR(36))+'_PDF' AS [GUID]
    FROM T_PDF PM
    INNER JOIN T_PRODUCT_ALIAS_NAMES PAN
     ON PM.F_PRODUCT = PAN.F_ALIAS
    INNER JOIN T_PROD_GROUPING PG
     ON PG.F_PRODUCT = PAN.F_PRODUCT
    INNER JOIN T_PRODUCT_GROUPS PGN
     ON PG.F_PRODUCT_GROUP = PGN.F_PRODUCT_GROUP
    WHERE
     (@P_NAME IS NULL OR PM.F_PRODUCT_NAME LIKE @P_NAME)
     AND (@S_FORMAT IS NULL OR PM.F_SUBFORMAT = @S_FORMAT OR @S_FORMAT = '-1')
     AND (@LANGUAGE IS NULL OR PM.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
     AND (@C1 IS NULL OR PM.F_CUSTOM1 LIKE @C1)
     AND ( -- a
      @C2 IS NULL
      OR PM.F_CUSTOM2 LIKE @CUSTOM2
      OR ( -- b
       @C2 IS NOT NULL AND EXISTS ( -- c
        SELECT 1 FROM #RPDS_CUSTOM2 RPDS_CUSTOM2
        WHERE RPDS_CUSTOM2.F_DATA LIKE @c2 -- Is LIKE appropriate here?
         AND RPDS_CUSTOM2.F_ALIAS = PM.F_PRODUCT
        ) -- c
      ) -- b
     ) -- a
    AND PM.F_AUTHORIZED != 0
    AND (@PRDGRP IS NULL OR PG.F_PRODUCT_GROUP LIKE @PRDGRP OR @PRDGRP = '-1') -- Is LIKE appropriate here?
    AND PG.F_PRODUCT_GROUP LIKE '%WV'

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

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

Viewing 4 posts - 1 through 3 (of 3 total)

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