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