TOP Clause performance is slow

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  February 17, 2019 at 4:02 am

  #370713

  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: 182882

  More actions

  February 17, 2019 at 6:02 am

  #2022218

  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: 1003861

  More actions

  February 17, 2019 at 7:34 am

  #2022223

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• jkramprakash

  SSCommitted

  Points: 1870

  More actions

  February 17, 2019 at 9:37 am

  #2022225

  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: 18259

  More actions

  February 18, 2019 at 1:30 pm

  #2022309

  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: 1003861

  More actions

  February 18, 2019 at 5:58 pm

  #2022338

  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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)