March 7, 2017 at 8:03 am
Hi,
Below is the my sample table schema for the Product. I will need to create search functionality on these columns(FullProductName, CompleteDescription, UniversalProductCodes, ProductBrand). Assume I have 0.5 million products in my sample database.
I don’t want to enable fulltext search. I have to handle this on stored procedure level with custom r search logic.
From the front end user can enter anything (FullProductName, CompleteDescription, UniversalProductCodes, ProductBrand).
Create table FullProduct(FullProductId bigint identity(1,1) primakey key,FullProductName varchar(100),CompleteDescription varchar(max), UniversalProductCodes varchar(12), ProductBrand varchar(50))
Questions:
To speed up the search logic on what columns I need to create Indexes.
Please help me on how to write the sample and fastest search logic without using sqlserver fulltext search.
Can the search logic be used without Dynamic sql? Because parameter will be passed from front end.
Can anyone please share your expertise here.
March 7, 2017 at 8:18 am
No index will help for this. You'll probably want to search inside the strings, so that would make the queries non-SARGable. Full Text Search would work great for the CompleteDescription column (which I wouldn't consider a varchar(max)).
Why do you think that you need dynamic SQL? It's a simple query with multiple conditions on the WHERE clause.
EDIT: If you want it to be fast, you need a more complex design. You need to give the products more attributes, include them in categories, and use different catalogs to fully identify what kind of product you need. Also, use a table to store the brands and link to it.
EDIT 2: To realize what 8000 characters look like, try this page: http://www.lipsum.com/
March 7, 2017 at 8:44 am
Hi Luis,
Thank you for the reply and I just checked the ProductCompleteDescription has 4000 characters limit. not varchar(max)
Also, you mean to say index will not help for searching varchar datatype ? please Assume if i create non clustered non unique index on individual search columns would that help
Can you please give me some sample sql query for my sample schema to test with 0.5 million records.
March 7, 2017 at 9:19 am
What I mean is that the usage of an index is only possible when you search for the beginning of a string. Check some examples in here: http://www.sqlservercentral.com/articles/T-SQL/130558/ (Note that the author is sloppy and made some mistakes that are addressed in the comments).
You can find an example on a product database in the sample databases provided by Microsoft such as AdventureWorks.
March 8, 2017 at 10:59 am
Hi Luis,
As you suggested i have tried to download the adventure database and found the below stored procedure has the logic of search. but look like sqlserver fulltext search being used here. I need to try without that. is there any there suggestion or sample would help me.
USE [AdventureWorks2008R2]
GO
/****** Object: StoredProcedure [dbo].[uspSearchCandidateResumes] Script Date: 03/08/2017 12:49:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--A stored procedure which demonstrates integrated full text search
CREATE PROCEDURE [dbo].[uspSearchCandidateResumes]
@searchString [nvarchar](1000),
@useInflectional [bit]=0,
@useThesaurus [bit]=0,
@language[int]=0
WITH EXECUTE AS CALLER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @string nvarchar(1050)
--setting the lcid to the default instance LCID if needed
IF @language = NULL OR @language = 0
BEGIN
SELECT @language =CONVERT(int, serverproperty('lcid'))
END
--FREETEXTTABLE case as inflectional and Thesaurus were required
IF @useThesaurus = 1 AND @useInflectional = 1
BEGIN
SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN FREETEXTTABLE([HumanResources].[JobCandidate],*, @searchString,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.
END
ELSE IF @useThesaurus = 1
BEGIN
SELECT @string ='FORMSOF(THESAURUS,"'+@searchString +'"'+')'
SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*, @string,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.
END
ELSE IF @useInflectional = 1
BEGIN
SELECT @string ='FORMSOF(INFLECTIONAL,"'+@searchString +'"'+')'
SELECT FT_TBL.[JobCandidateID], KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*, @string,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.
END
ELSE --base case, plain CONTAINSTABLE
BEGIN
SELECT @string='"'+@searchString +'"'
SELECT FT_TBL.[JobCandidateID],KEY_TBL.[RANK] FROM [HumanResources].[JobCandidate] AS FT_TBL
INNER JOIN CONTAINSTABLE([HumanResources].[JobCandidate],*,@string,LANGUAGE @language) AS KEY_TBL
ON FT_TBL.[JobCandidateID] =KEY_TBL.
END
END;
GO
March 8, 2017 at 11:21 am
The suggestion about AdventureWorks was to help you design your database. Currently, your problem is on the design, not the code.
March 8, 2017 at 12:38 pm
Thank you for the inputs Luis. i will go through throughtly
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply