October 15th, 2003, 03:06 PM
The moment you do something like this:
LIKE '%" +Prod__Search2+ "%'
then SQL has no choice, but to do a full table scan, because you have wildcards on both sides of the expression. If you did something like this instead:
LIKE Prod__Search2+ "%'
then the SQL engine could potentially use an index, since the wildcard is only on the right. However, since you have wild cards on both sides of the words to be searched, there's no way to prevent a table scan.
The only solution that occurs to me is if you can break up the phrase into individual words and then xref back to the phrase table. Then u can search for the words with = instead of LIKE and make the joins much faster.
Up the Irons
What Would Jimi Do? Smash amps. Burn guitar. Take the groupies home.
"Death Before Dishonour, my Friends!!" - Bruce D ickinson, Iron Maiden Aug 20, 2005 @ OzzFest
Down with Sharon Osbourne