TOP Clause performance is slow

  • jkramprakash

    SSCommitted

    Points: 1870

    I have 2 tables combined with union all operator in procedure.First table contains 20 million records  and second table contain 1 million records.It takes 11 sec for PRODUCTNAME search for particular condition Without using TOP Command .But when i used TOP 1000 for each table,it takes nearly 35 to 37 sec for PRODUCTNAME Search.How can we optimize the TOP Command?is there any alternative for top command.?Pls help.

    CREATE PROCEDURE [dbo].[TEST]
      @LANGUAGE NVARCHAR(2),
      @SUBFORMAT NVARCHAR(50),
      @PRODUCTNAME NVARCHAR(200),
    AS
    BEGIN
      SET NOCOUNT ON

      SELECT TOP 1000
       TP.F_PRODUCT AS ID,
       TP.F_PRODUCT_NAME AS [NAME],
       TP.F_LANGUAGE AS LANGCODE,
       TP.F_FORMAT AS FMTCODE,
       TP.F_CUSTOM1 AS TN,
       TP.F_CUSTOM2 AS CP
      FROM
       T_PDF TP
      LEFT JOIN
       V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT
      WHERE
       TP.F_PRODUCT <> ''
       AND (@PRODUCTNAME IS NULL OR
      TP.F_PRODUCT_NAME
      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
     AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
    AND EXISTS (SELECT 1 FROM AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)

    UNION ALL

    SELECT TOP 1000
       TP.F_PRODUCT AS ID,
       TP.F_PRODUCT_NAME AS [NAME],
       TP.F_LANGUAGE AS LANGCODE,
       TP.F_FORMAT AS FMTCODE,
       TP.F_CUSTOM1 AS TN,
       TP.F_CUSTOM2 AS CP
      FROM
       T_HTML TP
      LEFT JOIN
       V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT
      WHERE
       TP.F_PRODUCT <> ''
       AND (@PRODUCTNAME IS NULL OR
      TP.F_PRODUCT_NAME 
      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
    AND (@LANGUAGE IS NULL OR TP.F_LANGUAGE = @LANGUAGE OR @LANGUAGE = '-1')
    AND EXISTS (SELECT 1 FROM AllSubformats ASF WHERE ASF.Val = TP.F_SUBFORMAT)

    END

    CREATE INDEX IX_PRODNAME ON T_PDF(F_PRODUCT_NAME)
    CREATE INDEX IX_PRODNAME ON T_HTML(F_PRODUCT_NAME)

    First Table Structure

    CREATE TABLE [dbo].[T_PDF]
    (
      [F_PRODUCT] [varchar](50) NOT NULL,
      [F_LANGUAGE] [varchar](2) NOT NULL,
      [F_PRODUCT_NAME] [nvarchar](2000) NULL,
      [F_FORMAT] [varchar](3) NOT NULL,
      [F_SUBFORMAT] NVARCHAR(10),
      [F_CUSTOM1] [nvarchar](4000) NULL,
      [F_CUSTOM2] [nvarchar](4000) NULL,
      [F_CUSTOM3] [nvarchar](4000) NULL,
      [F_CUSTOM4] [nvarchar](4000) NULL,
      [F_CUSTOM5] [nvarchar](4000) NULL,
      GUID unique identifier

      CONSTRAINT [PK_T_PDF]
        PRIMARY KEY CLUSTERED ([F_PRODUCT] ASC,
               [F_LANGUAGE] ASC,
               [F_FORMAT] ASC,
               [F_SUBFORMAT] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    jkramprakash - Sunday, February 17, 2019 4:02 AM

    I have 2 tables combined with union all operator in procedure.First table contains 20 million records  and second table contain 1 million records.It takes 11 sec for PRODUCTNAME search for particular condition Without using TOP Command .But when i used TOP 1000 for each table,it takes nearly 35 to 37 sec for PRODUCTNAME Search.How can we optimize the TOP Command?is there any alternative for top command.?Pls help.

    CREATE PROCEDURE [dbo].[TEST]
      @LANGUAGE NVARCHAR(2),
      @SUBFORMAT NVARCHAR(50),
      @PRODUCTNAME NVARCHAR(200),
    AS
    BEGIN
      SET NOCOUNT ON

      SELECT TOP 1000
       TP.F_PRODUCT AS ID,
       TP.F_PRODUCT_NAME AS [NAME],
       TP.F_LANGUAGE AS LANGCODE,
       TP.F_FORMAT AS FMTCODE,
       TP.F_CUSTOM1 AS TN,
       TP.F_CUSTOM2 AS CP,
      FROM
       T_PDF TP
      LEFT JOIN
       V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT
      WHERE
       TP.F_PRODUCT <> ''
       AND (@PRODUCTNAME IS NULL OR
      TP.F_PRODUCT_NAME
      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

    UNION ALL

    SELECT TOP 1000
       TP.F_PRODUCT AS ID,
       TP.F_PRODUCT_NAME AS [NAME],
       TP.F_LANGUAGE AS LANGCODE,
       TP.F_FORMAT AS FMTCODE,
       TP.F_CUSTOM1 AS TN,
       TP.F_CUSTOM2 AS CP,
      FROM
       T_HTML TP
      LEFT JOIN
       V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT
      WHERE
       TP.F_PRODUCT <> ''
       AND (@PRODUCTNAME IS NULL OR
      TP.F_PRODUCT_NAME 
      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
    END

    CREATE INDEX IX_PRODNAME ON T_PDF(F_PRODUCT_NAME)
    CREATE INDEX IX_PRODNAME ON T_HTML(F_PRODUCT_NAME)

    First Table Structure

    CREATE TABLE [dbo].[T_PDF]
    (
      [F_PRODUCT] [varchar](50) NOT NULL,
      [F_LANGUAGE] [varchar](2) NOT NULL,
      [F_PRODUCT_NAME] [nvarchar](2000) NULL,
      [F_FORMAT] [varchar](3) NOT NULL,
      [F_SUBFORMAT] NVARCHAR(10),
      [F_CUSTOM1] [nvarchar](4000) NULL,
      [F_CUSTOM2] [nvarchar](4000) NULL,
      [F_CUSTOM3] [nvarchar](4000) NULL,
      [F_CUSTOM4] [nvarchar](4000) NULL,
      [F_CUSTOM5] [nvarchar](4000) NULL,
      GUID unique identifier

      CONSTRAINT [PK_T_PDF]
        PRIMARY KEY CLUSTERED ([F_PRODUCT] ASC,
               [F_LANGUAGE] ASC,
               [F_FORMAT] ASC,
               [F_SUBFORMAT] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    Can you post the execution plan please?
    😎
    Further, the code you've posted is incomplete and contains syntax errors!

  • Jeff Moden

    SSC Guru

    Points: 993897

    jkramprakash - Sunday, February 17, 2019 4:02 AM

    I have 2 tables combined with union all operator in procedure.First table contains 20 million records  and second table contain 1 million records.It takes 11 sec for PRODUCTNAME search for particular condition Without using TOP Command .But when i used TOP 1000 for each table,it takes nearly 35 to 37 sec for PRODUCTNAME Search.How can we optimize the TOP Command?is there any alternative for top command.?Pls help.

    CREATE PROCEDURE [dbo].[TEST]
      @LANGUAGE NVARCHAR(2),
      @SUBFORMAT NVARCHAR(50),
      @PRODUCTNAME NVARCHAR(200),
    AS
    BEGIN
      SET NOCOUNT ON

      SELECT TOP 1000
       TP.F_PRODUCT AS ID,
       TP.F_PRODUCT_NAME AS [NAME],
       TP.F_LANGUAGE AS LANGCODE,
       TP.F_FORMAT AS FMTCODE,
       TP.F_CUSTOM1 AS TN,
       TP.F_CUSTOM2 AS CP,
      FROM
       T_PDF TP
      LEFT JOIN
       V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT
      WHERE
       TP.F_PRODUCT <> ''
       AND (@PRODUCTNAME IS NULL OR
      TP.F_PRODUCT_NAME
      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

    UNION ALL

    SELECT TOP 1000
       TP.F_PRODUCT AS ID,
       TP.F_PRODUCT_NAME AS [NAME],
       TP.F_LANGUAGE AS LANGCODE,
       TP.F_FORMAT AS FMTCODE,
       TP.F_CUSTOM1 AS TN,
       TP.F_CUSTOM2 AS CP,
      FROM
       T_HTML TP
      LEFT JOIN
       V_PROD_ALIAS_SYN SYN ON SYN.F_PRODUCT = TP.F_PRODUCT
      WHERE
       TP.F_PRODUCT <> ''
       AND (@PRODUCTNAME IS NULL OR
      TP.F_PRODUCT_NAME 
      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
    END

    CREATE INDEX IX_PRODNAME ON T_PDF(F_PRODUCT_NAME)
    CREATE INDEX IX_PRODNAME ON T_HTML(F_PRODUCT_NAME)

    First Table Structure

    CREATE TABLE [dbo].[T_PDF]
    (
      [F_PRODUCT] [varchar](50) NOT NULL,
      [F_LANGUAGE] [varchar](2) NOT NULL,
      [F_PRODUCT_NAME] [nvarchar](2000) NULL,
      [F_FORMAT] [varchar](3) NOT NULL,
      [F_SUBFORMAT] NVARCHAR(10),
      [F_CUSTOM1] [nvarchar](4000) NULL,
      [F_CUSTOM2] [nvarchar](4000) NULL,
      [F_CUSTOM3] [nvarchar](4000) NULL,
      [F_CUSTOM4] [nvarchar](4000) NULL,
      [F_CUSTOM5] [nvarchar](4000) NULL,
      GUID unique identifier

      CONSTRAINT [PK_T_PDF]
        PRIMARY KEY CLUSTERED ([F_PRODUCT] ASC,
               [F_LANGUAGE] ASC,
               [F_FORMAT] ASC,
               [F_SUBFORMAT] ASC)
          WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
            IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
            ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    We've seen your code before (https://www.sqlservercentral.com/Forums/2019077/Tuning-Query-In-Stored-Procedure). As previously explained, your code is riddled with non-SARGable predicates and many suggestions were made to resolve that issue.  What have you actually done to even try  resolve those issues because that's what going to need to happen to get TOP or anything else about this query to perform well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

  • jkramprakash

    SSCommitted

    Points: 1870

    As per the suggestion of previous post,now i am testing by removing the replace functions in the
    TP.F_PRODUCT_NAME column(REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME, '™', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG'))in this procedure.I am using TP.F_PRODUCT_NAME directly without replace function for performance testing.Now our requirement is changed,actually the procedure takes 3 minutes to return all the rows(millions of rows) when we not passed any values to all the parameters.So i am using TOP 1000 command for both tables to return 1000 records from each table.Now it is working fine when we not passed any values to the parameters.When i passed value to F_PRODUCT_NAME it takes 35 sec(250 rows) for TOP 1000 clause and it takes 10 sec(250 rows) when i not used TOP clause. Anything i have do for efficient use of TOP clause?or any other alternative for TOP clause to return 1000 records?

  • Jonathan AC Roberts

    SSCoach

    Points: 16685

    jkramprakash - Sunday, February 17, 2019 9:37 AM

    As per the suggestion of previous post,now i am testing by removing the replace functions in the
    TP.F_PRODUCT_NAME column(REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME, 'â„¢', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG'))in this procedure.I am using TP.F_PRODUCT_NAME directly without replace function for performance testing.Now our requirement is changed,actually the procedure takes 3 minutes to return all the rows(millions of rows) when we not passed any values to all the parameters.So i am using TOP 1000 command for both tables to return 1000 records from each table.Now it is working fine when we not passed any values to the parameters.When i passed value to F_PRODUCT_NAME it takes 35 sec(250 rows) for TOP 1000 clause and it takes 10 sec(250 rows) when i not used TOP clause. Anything i have do for efficient use of TOP clause?or any other alternative for TOP clause to return 1000 records?

    Using the TOP clause can change the execution plan. That's why Eirikur asked for the execution plan. If you can get the both the plan when it's performing well and when it's not it would be something to work with.

  • Jeff Moden

    SSC Guru

    Points: 993897

    jkramprakash - Sunday, February 17, 2019 9:37 AM

    As per the suggestion of previous post,now i am testing by removing the replace functions in the
    TP.F_PRODUCT_NAME column(REPLACE(REPLACE(REPLACE(REPLACE(TP.F_PRODUCT_NAME, 'â„¢', '|TM'), '®', '|TS'), '©', '|CP'), '°', '|DEG'))in this procedure.I am using TP.F_PRODUCT_NAME directly without replace function for performance testing.Now our requirement is changed,actually the procedure takes 3 minutes to return all the rows(millions of rows) when we not passed any values to all the parameters.So i am using TOP 1000 command for both tables to return 1000 records from each table.Now it is working fine when we not passed any values to the parameters.When i passed value to F_PRODUCT_NAME it takes 35 sec(250 rows) for TOP 1000 clause and it takes 10 sec(250 rows) when i not used TOP clause. Anything i have do for efficient use of TOP clause?or any other alternative for TOP clause to return 1000 records?

    The fact is, the query you posted still has all of the performance issues that it had before.  You could try using OPTION(RECOMPILE) to try to get the same performance as not passing the parameter but your code is still going to be slow no matter what because of all the non-SARGable predicates you have.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 6 posts - 1 through 6 (of 6 total)

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