I have an app that I'm adding a generic search function to. The user types in a word, or phrase, or portion of a word, and all items matching this search string anywhere in the database is returned. No surprise, it's running like a dog. Indexes may help, but not for the leading wildcards. Here's the WHERE clause:
I would take a slightly different approach than Celko. Just don't do this at all. There is no such thing as a third party app that can return a value that it finds in any column or row in the database in anything resembling a reasonable amount of time. At the very least you need to make the user identify what to search. This type of catch all search will never be anything but horribly slow.
You might want to read this article on Gail's blog about catch-all queries. I think you need to convert to something along the lines of what she explains. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/