how to increase performance?

Hi, I am using this MS SQL statement:

"SELECT * FROM dbo.Products INNER JOIN dbo.InvPrice ON dbo.Products.ProductCode = dbo.InvPrice.ProductCode WHERE (dbo.Products.ProductCode LIKE '%" +Prod__Search2+ "%' AND Supplier LIKE '" + Prod__Select2+ "' OR Description LIKE '%" + Prod__Search2+ "%' AND Supplier LIKE '" + Prod__Select2+ "') AND (dbo.InvPrice.PriceCode = 'AA')"

For an asp search page, but the results takes a long time to display. If I take out the wildcard '%' eg. i change (...LIKE '%" +Prod__Search2+ "%'...) to (...LIKE '" + Prod__Search2+ "'...) for an exact phrase search, the results show up instantaneous, but I don't want an exact phrase search.

My database consists of 250,000 records. Any solutions?

Thanks.

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.

What if the search is not for a phrase but for a code? eg. PC-AK04
PC09232, PC-add32, etc... and when the user types in PC, all those queries containing PC in front would come up.

I have tried useing '%' on one side only, but the performance is still slow.

thanks.