Optimizing queries that search for text

Question

I have SSRS report that uses a query that searches for specific text in the Where clause. I know using IDs is the preferred method of filtering data down, but this table only makes use of varchar strings...Very long strings I might add.

There are 5 different varchar strings that the query searches for. The strings are static, so there will be no change in the string text (Thankfully). The query itself is very straightforward. I know using wildcards would affect performance for the worse. I did some research and it seems like there's not much I can do. Before I give up, I was hoping the smart folks at Stackoverflow may have an idea of what I could do to optimize the query.

SELECT * FROM
Reviews
WHERE ReviewFieldOption = 'The student displayed proficient knowledge of the material provided over the semester'

Answer 1

For your query, you want in index on ReviewFieldOption.

This works for strings, even long strings -- assuming they are not tooooo long. There are some limits on the size of keys in an index.