Are there any better search methods 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.

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

I'm using stored procedures now, but linq to sql or entity framework are also a possibility.

Answer 1

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 2

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 + '%'