How to reduce the query execution time with full text search and huge data

Question

5.00/5 (2 votes)

See more:

Here is the structure of my table:

CREATE TABLE [dbo].[TradeMark](
	[SerialNo] [nvarchar](250) NOT NULL,
	[RegistrationNo] [nvarchar](250) NOT NULL,
	[MarkIdentification] [nvarchar](max) NULL,
	[Status] [int] NULL,
	[USClassId] [nvarchar](1000) NOT NULL,
	[TypeCode] [nvarchar](100) NULL,
	[Casefilestatementstext] [nvarchar](max) NULL,
	[OwnerName] [nvarchar](200) NULL,
	[OwnerType] [int] NULL,
	[Filename] [nvarchar](1000) NOT NULL,
	[PreviousFileName] [nvarchar](200) NULL,
	[UpdateIteration] [int] NOT NULL CONSTRAINT [DF_TradeMark_UpdateIteration]  DEFAULT ((0)),
	[NewCasefilestatementstext] [nvarchar](max) NULL,
	[TrademarkId] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_Trademark_TrademarkId] PRIMARY KEY CLUSTERED 
(
	[TrademarkId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UK_Trademark_SerialNo] UNIQUE NONCLUSTERED 
(
	[SerialNo] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

The table contains more than 3 million records and I need to apply a full text search query (actually a very huge query). here is the query and it's response:

SQL

select Top 10 SerialNo, Registrationno,Status,MarkIdentification,OwnerName,Casefilestatementstext from Trademark where 
(MarkIdentification LIKE '% CALIDOUBLE %' 
OR MarkIdentification LIKE 'CALIDOUBLE %' 
OR MarkIdentification LIKE 'CALIDOUBLEs %' 
OR MarkIdentification LIKE '% CALIDOUBLEs %' 
OR MarkIdentification LIKE 'CALIDOUBLE''s %' 
OR MarkIdentification LIKE 'CALIDOUBLE''s%'  
OR MarkIdentification LIKE 'CALIDOUBLE' 
OR MarkIdentification LIKE '%-CALIDOUBLE' 
OR MarkIdentification LIKE '% CALIDOUBLE')  
AND ( 
USClassId like  '%001%' OR USClassId like  '%023%' OR USClassId like  '%027%' OR USClassId like  '%034%' 
OR USClassId like  '%038%' OR USClassId like  '%039%' OR USClassId like  '%045%' OR USClassId like  '%046%' 
OR USClassId like  '%047%' OR USClassId like  '%048%' OR USClassId like  '%100%' OR USClassId like  '%101%' 
OR USClassId like  '%104%' OR USClassId like  '%105%' OR USClassId like  '%107%' ) 
AND ((Casefilestatementstext like '%Ale%') 
OR(Casefilestatementstext like '%Ales%') 
OR(Casefilestatementstext like '%Apron%') 
OR(Casefilestatementstext like '%Aprons%') 
OR(Casefilestatementstext like '%Barbeque%') 
OR(Casefilestatementstext like '%Barbeques%') 
OR(Casefilestatementstext like '%Beany%') 
OR(Casefilestatementstext like '%Beanies%') 
OR(Casefilestatementstext like '%Beer%') 
OR(Casefilestatementstext like '%Beers%') 
OR(Casefilestatementstext like '%Beverage%') 
OR(Casefilestatementstext like '%Beverages%') 
OR(Casefilestatementstext like '%Blouse%') 
OR(Casefilestatementstext like '%Blouses%') 
OR(Casefilestatementstext like '%Bologna%') 
OR(Casefilestatementstext like '%Bolognas%') 
OR(Casefilestatementstext like '%Boot%') 
OR(Casefilestatementstext like '%Boots%') 
OR(Casefilestatementstext like '%Bread%') 
OR(Casefilestatementstext like '%Breads%') 
OR(Casefilestatementstext like '%Burger%') 
OR(Casefilestatementstext like '%Burgers%') 
OR(Casefilestatementstext like '%Cake%') 
OR(Casefilestatementstext like '%Cakes%') 
OR(Casefilestatementstext like '%Candy%') 
OR(Casefilestatementstext like '%Candies%') 
OR(Casefilestatementstext like '%Cap%') 
OR(Casefilestatementstext like '%Caps%') 
OR(Casefilestatementstext like '%Catsup%') 
OR(Casefilestatementstext like '%Catsups%') 
OR(Casefilestatementstext like '%Cattle%') 
OR(Casefilestatementstext like '%Cattle%') 
OR(Casefilestatementstext like '%Cheese%') 
OR(Casefilestatementstext like '%Cheese%') 
OR(Casefilestatementstext like '%Clock%') 
OR(Casefilestatementstext like '%Clocks%') 
OR(Casefilestatementstext like '%Coat%') 
OR(Casefilestatementstext like '%Coats%') 
OR(Casefilestatementstext like '%Coffee%') 
OR(Casefilestatementstext like '%Coffees%') 
OR(Casefilestatementstext like '%Collar%') 
OR(Casefilestatementstext like '%Collars%') 
OR(Casefilestatementstext like '%Cuff%') 
OR(Casefilestatementstext like '%Cuffs%') 
OR(Casefilestatementstext like '%Dress%') 
OR(Casefilestatementstext like '%Dresses%') 
OR(Casefilestatementstext like '%Espresso%') 
OR(Casefilestatementstext like '%Espressoes%') 
OR(Casefilestatementstext like '%Falafel%')
OR(Casefilestatementstext like '%Falafels%')
OR(Casefilestatementstext like '%Fish%')
OR(Casefilestatementstext like '%Fish%') 
OR(Casefilestatementstext like '%Flour%')
OR(Casefilestatementstext like '%Flours%')
OR(Casefilestatementstext like '%Food%')
OR(Casefilestatementstext like '%Foods%') 
OR(Casefilestatementstext like '%Frankfurter%')
OR(Casefilestatementstext like '%Frankfurters%')
OR(Casefilestatementstext like '%Ham%') 
OR(Casefilestatementstext like '%Hams%')
OR(Casefilestatementstext like '%Hamburger%')
OR(Casefilestatementstext like '%Hamburgers%')
OR(Casefilestatementstext like '%Hat%') 
OR(Casefilestatementstext like '%Hats%')
OR(Casefilestatementstext like '%Jacket%')
OR(Casefilestatementstext like '%Jackets%')
OR(Casefilestatementstext like '%Jean%') 
OR(Casefilestatementstext like '%Jeans%')
OR(Casefilestatementstext like '%Magazine%')
OR(Casefilestatementstext like '%Magazines%') 
OR(Casefilestatementstext like '%Marinade%')
OR(Casefilestatementstext like '%Marinades%')
OR(Casefilestatementstext like '%Meatball%') 
OR(Casefilestatementstext like '%Meatballs%')
OR(Casefilestatementstext like '%Milk%')
OR(Casefilestatementstext like '%Milk%')
OR(Casefilestatementstext like '%Mustard%') 
OR(Casefilestatementstext like '%Mustards%')
OR(Casefilestatementstext like '%Pajama%')
OR(Casefilestatementstext like '%Pajamas%')
OR(Casefilestatementstext like '%Pant%') 
OR(Casefilestatementstext like '%Pants%')
OR(Casefilestatementstext like '%Pasta%')
OR(Casefilestatementstext like '%Pastas%')
OR(Casefilestatementstext like '%Pecan%') 
OR(Casefilestatementstext like '%Pecans%')
OR(Casefilestatementstext like '%Pepperoni%')
OR(Casefilestatementstext like '%Pepperonis%')
OR(Casefilestatementstext like '%Pickle%') 
OR(Casefilestatementstext like '%Pickles%')
OR(Casefilestatementstext like '%Pie%')
OR(Casefilestatementstext like '%Pies%')
OR(Casefilestatementstext like '%Pizza%') 
OR(Casefilestatementstext like '%Pizzas%')
OR(Casefilestatementstext like '%Poultry%')
OR(Casefilestatementstext like '%Poultries%')
OR(Casefilestatementstext like '%Rice%') 
OR(Casefilestatementstext like '%Rice%')
OR(Casefilestatementstext like '%Roast%')
OR(Casefilestatementstext like '%Roasts%')
OR(Casefilestatementstext like '%Roll%') 
OR(Casefilestatementstext like '%Rolls%')
OR(Casefilestatementstext like '%Salami%')
OR(Casefilestatementstext like '%Salamis%')
OR(Casefilestatementstext like '%Salsa%') 
OR(Casefilestatementstext like '%Salsas%')
OR(Casefilestatementstext like '%Sandwich%')
OR(Casefilestatementstext like '%Sandwiches%')
OR(Casefilestatementstext like '%Sauce%') 
OR(Casefilestatementstext like '%Sauces%')
OR(Casefilestatementstext like '%Sausage%')
OR(Casefilestatementstext like '%Sausages%')
OR(Casefilestatementstext like '%Seasoning%') 
OR(Casefilestatementstext like '%Seasonings%')
OR(Casefilestatementstext like '%Shirt%')
OR(Casefilestatementstext like '%Shirts%')
OR(Casefilestatementstext like '%Short%') 
OR(Casefilestatementstext like '%Shorts%')
OR(Casefilestatementstext like '%Slack%')
OR(Casefilestatementstext like '%Slacks%')
OR(Casefilestatementstext like '%Smoothy%') 
OR(Casefilestatementstext like '%Smoothies%')
OR(Casefilestatementstext like '%Spice%')
OR(Casefilestatementstext like '%Spices%')
OR(Casefilestatementstext like '%Stout%') 
OR(Casefilestatementstext like '%Stouts%')
OR(Casefilestatementstext like '%Sugar%')
OR(Casefilestatementstext like '%Sugars%')
OR(Casefilestatementstext like '%Suit%') 
OR(Casefilestatementstext like '%Suits%')
OR(Casefilestatementstext like '%Sweater%')
OR(Casefilestatementstext like '%Sweaters%')
OR(Casefilestatementstext like '%Sweatshirt%') 
OR(Casefilestatementstext like '%Sweatshirts%')
OR(Casefilestatementstext like '%Syrup%')
OR(Casefilestatementstext like '%Syrups%')
OR(Casefilestatementstext like '%Tie%') 
OR(Casefilestatementstext like '%Ties%')
OR(Casefilestatementstext like '%Trouser%')
OR(Casefilestatementstext like '%Trousers%')
OR(Casefilestatementstext like '%T-shirts%') 
OR(Casefilestatementstext like '%T-shirts%')
OR(Casefilestatementstext like '%Underwear%')
OR(Casefilestatementstext like '%Underwears%')
OR(Casefilestatementstext like '%Watch%') 
OR(Casefilestatementstext like '%Watches%')
OR(Casefilestatementstext like '%Wine%')
OR(Casefilestatementstext like '%Wines%') 
) 
AND (
OR( CONTAINS(Casefilestatementstext,'NEAR((Bakery,good), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Bakeries,good), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Bakery,goods), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Bakeries,goods), 6)'))  
OR( CONTAINS(Casefilestatementstext,'NEAR((Bean,garbanzo,processed), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beans,garbanzo,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Bean,garbanzoes,processed), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beans,garbanzoes,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Bean,garbanzo,processeds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beans,garbanzo,processeds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Bean,garbanzoes,processeds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beans,garbanzoes,processeds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((beauty,contest), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((beauties,contest), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((beauty,contests), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((beauties,contests), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patty), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patty), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patties), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patties), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minced), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minced), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minceds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minceds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processeds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processeds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((beverage,station), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((beverages,station), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((beverage,stations), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((beverages,stations), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverage,maltless), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverages,maltless), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverage,maltlesses), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverages,maltlesses), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverage,non-alcoholic), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverages,non-alcoholic), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverage,non-alcoholic), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beverages,non-alcoholic), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((boat,cruis), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((boats,cruis), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((boat,cruises), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((boats,cruises), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Bread,mix), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Breads,mix), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Bread,mixes), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Breads,mixes), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Bread,pocket), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Breads,pocket), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Bread,pockets), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Breads,pockets), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Cake,mix), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Cakes,mix), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Cake,mixes), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Cakes,mixes), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,beverage), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,beverage), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,beverage), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,beverage), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,beverages), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,beverages), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,beverages), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,beverages), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,food), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,food), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,food), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,food), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,foods), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,foods), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((catering,foods), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((caterings,foods), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Chicken,prepared), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Chickens,prepared), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Chicken,prepareds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Chickens,prepareds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Chickpea,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Chickpeas,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Chickpea,processeds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Chickpeas,processeds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((coffee,certification), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((coffees,certification), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((coffee,certifications), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((coffees,certifications), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((coffee,shop), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((coffees,shop), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((coffee,shops), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((coffees,shops), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((concession,stand), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((concessions,stand), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((concession,stands), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((concessions,stands), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((convenience,store), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((conveniences,store), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((convenience,stores), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((conveniences,stores), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((cooking,cours), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((cookings,cours), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((cooking,courses), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((cookings,courses), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((cooking,lecture), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((cookings,lecture), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((cooking,lectures), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((cookings,lectures), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Cottage,cheese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Cottages,cheese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Cottage,cheeses), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Cottages,cheeses), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Custard,frozen), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Custards,frozen), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Custard,frozens), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Custards,frozens), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concert,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concert,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concert,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concert,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concerts,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concerts,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concerts,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concerts,production), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concert,productions), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concert,productions), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concert,productions), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concert,productions), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concerts,productions), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concerts,productions), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,concerts,productions), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,concerts,productions), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,firework), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,firework), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,firework), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,firework), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,fireworks), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,fireworks), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,fireworks), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,fireworks), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hosting,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hosting,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hosting,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hosting,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hostings,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hostings,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hostings,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hostings,party), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hosting,parties), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hosting,parties), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hosting,parties), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hosting,parties), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hostings,parties), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hostings,parties), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainment,hostings,parties), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((entertainments,hostings,parties), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,glaze), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,glaze), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,glazes), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,glazes), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rub,dry), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rub,dry), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rubs,dry), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rubs,dry), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rub,dries), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rub,dries), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rubs,dries), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fish,rubs,dries), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((food,delivery), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((foods,delivery), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((food,deliveries), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((foods,deliveries), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((food,preparation), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((foods,preparation), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((food,preparations), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((foods,preparations), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,product), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,product), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,products), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,products), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,product,Chinese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,product,Chinese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,products,Chinese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,products,Chinese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,product,Chinese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,product,Chinese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,products,Chinese), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,products,Chinese), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,product,frozen), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,product,frozen), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,products,frozen), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,products,frozen), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,product,frozens), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,product,frozens), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Food,products,frozens), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Foods,products,frozens), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Fruit,drink), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fruits,drink), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fruit,drinks), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fruits,drinks), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryer,deep,fat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryers,deep,fat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryer,deeps,fat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryers,deeps,fat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryer,deep,fats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryers,deep,fats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryer,deeps,fats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Fryers,deeps,fats), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmet,shop,restaurant), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmets,shop,restaurant), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmet,shops,restaurant), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmets,shops,restaurant), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmet,shop,restaurants), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmets,shop,restaurants), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmet,shops,restaurants), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((gourmets,shops,restaurants), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Hot,dog), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Hots,dog), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Hot,dogs), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Hots,dogs), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((hotel,reservation), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((hotels,reservation), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((hotel,reservations), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((hotels,reservations), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,cream), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,cream), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,creams), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,creams), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bar), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bar), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bar), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bar), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bars), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bars), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bars), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,milk,bars), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavored,colored), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavored,colored), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavoreds,colored), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavoreds,colored), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavored,coloreds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavored,coloreds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavoreds,coloreds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Ice,flavoreds,coloreds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shopping,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shopping,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shoppings,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shoppings,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shopping,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shopping,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shoppings,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shoppings,meat), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shopping,meats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shopping,meats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shoppings,meats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shoppings,meats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shopping,meats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shopping,meats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internet,shoppings,meats), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((internets,shoppings,meats), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((juice,bar), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((juices,bar), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((juice,bars), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((juices,bars), 6)')) 
/* 750 rows of same kind of full text search*/
OR( CONTAINS(Casefilestatementstext,'NEAR((Tacoes,shell), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Taco,shells), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Tacoes,shells), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Top,tank), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Tops,tank), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Top,tanks), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Tops,tanks), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((travel,agency), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((travels,agency), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((travel,agencies), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((travels,agencies), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Vegetable,dried), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Vegetables,dried), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Vegetable,drieds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Vegetables,drieds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Waffle,mix), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Waffles,mix), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Waffle,mixes), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Waffles,mixes), 6)'))
  )
Order by Registrationno desc, SerialNo desc

Here is the response of the query:
2017-08-29_1505[^]

How can I get the fast result with either optimize the query or Change the table structure?

What I have tried:

I have created unique key for column SerialNo, and a non-clustered index for Registrationno column.

I am afraid that too much indexes will reduce query performance.

Posted 28-Aug-17 23:40pm

Hitesh K Gupta

Updated 29-Aug-17 17:58pm

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2017-08-29T02:43:00

There is some redundancy:

SQL

OR(Casefilestatementstext like '%Apron%') 
OR(Casefilestatementstext like '%Aprons%')

The first statement matches also the next one. So there is no need to check for "Aprons" and similar for all the other checks in that block.

I know that this will not speed up much but it is an improvement.

[EDIT]
There are also some duplicates. Example:

SQL

OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patty), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patty), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patties), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,patties), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minced), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minced), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minceds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,minceds), 6)')) 
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processed), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processeds), 6)'))
OR( CONTAINS(Casefilestatementstext,'NEAR((Beef,processeds), 6)'))

[/EDIT]

Tom Wauters · Answer 2 · 2017-08-29T17:58:00

There could be a smal workaround for this. First of all lose the duplicates like Jochen said. If you have access to the DB structure you could create a new table with the id of the Trademark table and a flag of the searchstring it has. Meaning: beef =1, jacket =2,...
Grouping by 2 should go Faster than full text search. With the ID's you get you can fetch the rows in the Trademark table. However it produces some overhead because you always have to update the new table on changes to the Trademark table.
Like I said. It is a workaround and not going to fix the slow query.

How to reduce the query execution time with full text search and huge data

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0