Search Results are very slow when retreiving Varchar Records using like operator

Question

Email Match field = '@hotmail.com'

Email Match field entered: Results 1199 - speed of return 40 seconds

Email Match field not entered: Results 429 - speed of return 1.6 seconds

SELECT * FROM emails e where e.[From] like '%@hotmail.com%' OR e.[To] like '%@hotmail.com%'order by [id] DESC

I have to use % at start and end because i want to search this in email addresses

Thanks

Edit:

Execution plan of original query

Answer 1

Indexing and statistics is always the first thing you want to look at when optimizing performance, but for a more general approach targeting that exact query:

The OR clause will actually divide the query into two queries, one checking for each criteria, execute both, and then merge the results and filter out duplicates.

You might gain performance by a query such as

SELECT * FROM emails e
WHERE (e.From + '/' + e.To) LIKE '%@hotmail.com%'
ORDER BY id DESC

... although it's a bit uglier =) Try it out in your query analyzer and see what performs best...

EDIT

As Martin pointed out in comments, if the fields are nullable, the WHERE clause should have to be updated to accommodate that:

WHERE ( ISNULL(e.From,'') + '/' + ISNULL(e.To,'') ) LIKE '%@hotmail.com%'