如何使用全文搜索和大量数据减少查询执行时间 [英] How to reduce the query execution time with full text search and huge data

查看：81
本文介绍了如何使用全文搜索和大量数据减少查询执行时间的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

这是我的表的结构：

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



该表包含300多万条记录，我需要应用全文搜索查询（实际上是一个非常大的查询）。这是查询及其响应：




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:

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.



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.

推荐答案

There is some redundancy:

There is some redundancy:
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.





There are also some duplicates. Example:

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.


There are also some duplicates. Example:

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]


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.
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.


这篇关于如何使用全文搜索和大量数据减少查询执行时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆