CODEWITHSUNDEEP
sql-server-2005
user467710
user467710

Reputation: 33

Search results are very slow when retrieving Varchar Records using like operator

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

Edit:

alt text

alt text

alt text

Execution plan of original query

Upvotes: 2

Views: 422

Answers (1)

David Hedlund
David Hedlund

Reputation: 129792

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

Upvotes: 1

Related Questions