What is the best practice to search in SQL server with large data for a string

Question

0.00/5 (No votes)

See more:

Hi everyone

I wanted to know that what may be the best practice to search for keywords or sentence in sql server.

Suppose I have a table say products with name, description and keywords. I have a search function in my website where the user may type in anything (sentence or single word).
I query the product table columns using like clause and shortlist the list to rows containing the search criteria.
To reduce the response time in case if the volume of data in product table is large, I add a pagination query of 10.
Is the above explained scenario a good practice?

Thanks in advance

What I have tried:

My current query

select * from (select srno as productid, name +' '+ descriptions +' '+ keywords as search
from ProductDetails) PD
where PD.search LIKE '%bed%' OR PD.search LIKE '%sheets%'

Here I have combined the three columns namely name,descriptions and keywords and I am searching for "bed sheets" in my product table

Table Structure

ProductDetails example
srno|Name|Description|keywords
1|Floral Bed Sheets|..some text..|bed sheets
2|Square Pillow Covers|.. some text..|pillow covers square

Is it proper to place keywords in a separate column or should I create a new table for keywords.
If I have to create a new table for keywords, how to modify the above query?

Posted 28-Aug-16 3:45am

JPais

Updated 7-Sep-16 10:37am

v2

Add a Solution

Comments

an0ther1 28-Aug-16 19:50pm

Hi JPais,

Pagination will not help - the full result set needs to be identified and then the first 'x' number of records (10 in your case) is returned. It reduces the result set passed to the client but it doesn't speed up your query.
Additionally indexing will not help because you are using %search term%. SQL can use text prior to the first wildcard to identify a result set but that is all.
There is a few options though;
a) Implement categories in your product table - this will help limit your results.
b) Implement Full Text Search - this is probably the best option
c) Create a search table that contains the search words - this is basically re-creating full text search manually & I wouldn't recommend it.

Kind Regards

JPais 4-Sep-16 5:30am

Thank u for your reply.. can u pls help me with your third option. Actually I am using a shared server and iy does not allow me to set up full text server. So can you pls guide me through your third option.
Also will "like query" as the one in my query will slow the search process?

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)

NaibedyaKar · Answer 1 · 2016-09-07T10:37:00

Solution 2

I feel you show go for full text indexing.

Read more from Understanding Full-Text Indexing in SQL Server - Simple Talk[^].

Above to that you can implement fuzzy search algorithm for approximate string matching.

Posted 7-Sep-16 10:37am

NaibedyaKar

gregorio89 · Answer 2 · 2016-08-29T21:38:00

I used your second solution for a similar problems.
You can add a index to the fields containing the word searched and do this :

select srno as productid, name +' '+ descriptions +' '+ keywords as search
from ProductDetails as PD
where PD.search LIKE '%bed%' OR PD.search LIKE '%sheets%'

but I suggest you if it is possible to use only like in this way

select srno as productid, name +' '+ descriptions +' '+ keywords as search
from ProductDetails
where PD.search LIKE 'bed%' OR PD.search LIKE 'sheets%'

in this way you don't lost your index