c# - What is the best optimization technique for a wildcard search through 100,000 records in sql table -

i working on asp.net mvc application. application used 200 users. these users (every 5 mins) search item list of 100,000 items (this list going increase every month 1-2 %). list of 100,000 items stored in sql server table.

the search wildcard search

eg:

select itemcode, itemname, itemdesc  tblitems itemname '%searchword%' 

the searching needs fast since main business relies on searching , selecting item.

i know how best performance. search results have come instantaneously.

what have tried -

1. i tried pre-loading entire 100,000 records memcache , reading memcache. trying avoid calls sql server every search.

   this takes lot of time. every time user searches item, retrieving 100,000 records memcache , doing search. taking 2-3 times more time direct sql searches.

2. i tried doing direct search on sql server table limiting results 50 records @ time (using top 50)

   this seems ok still no-where near performance seeking

i hear possible solutions , links articles/code.

thanks in advance

run sql profiler , tuning profile. make recommendations on indexes execute against database.

also, query such following worth try.

select  *     (      select    row_number() on ( order columna) rownumber, itemcode, itemname, itemdesc          tblitems         itemname '%foobar%' ) rowresults   rownumber >= 1 , rownumber < 50 order rownumber 

edit: updated query reflect real scenario.