Is there any better search method instead of string.Contains("keyword") using asp.net?

Question

For example， the users of our app want to search products by entering a keyword of productName.

The Products table of the sql server database contains about 10 million records.

Is there any better and higher-performance search method implement instead of productName.Contains("keyword") using asp.net C# ?

I use store procedure now, but linq to sql or entity framework will be allow to use also.

Answer 1

Well you could achieve this from the db side using a LIKE

LIKE (Transact-SQL)

Something like

DECLARE @Table TABLE(
        Val VARCHAR(50)
)

INSERT INTO @Table SELECT 'asdf'
INSERT INTO @Table SELECT 'tada'
INSERT INTO @Table SELECT 'foo'
INSERT INTO @Table SELECT 'bar'
INSERT INTO @Table SELECT 'test'
INSERT INTO @Table SELECT 'test foo'

DECLARE @Lookup VARCHAR(50)
SELECT @Lookup = 'foo'

SELECT  *
FROM    @Table
WHERE   Val LIKE '%' + @Lookup + '%'

Answer 2

If you want better performance then you could look into a full text search. Note that this is currently not supported by LINQ to SQL but you can write the SQL in a stored procedure and call the stored procedure from your application. The accepted answer for this StackOverflow post recommends reading this article.

Answer 3

You should never fetch more data from your database than you need. So it is best for you to restrict your rows inside the data layer (ie. your stored procedure). You can do this by only returning matching rows:

SELECT * FROM Products WHERE ProductName LIKE '%' + @keyword + '%'

Where @keyword is a parameter to your store procedure.

But be aware that performance using LIKE can be slow, since it must read the entire table. You can better performance more by matching products that starts with the keyword:

SELECT * FROM Products WHERE ProductName LIKE @keyword + '%'

Note I removed the '%' before @keyword. This enables SQL Server to use an index on the ProductName column to find the rows much quicker!

Fore further help, please post your stored procedure...