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'

"...I know using IDs is the preferred method of filtering data down..." -- nope. Filtering is all about the flexibility of searching in any way that makes sense to the business model. How long do you expect these string to be? 1000 chars should be well supported, but I wouldn't go beyond 32k. — The Impaler, Feb 16 '21 at 21:59

score 1 · Accepted Answer · answered Feb 16 '21 at 18:27

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.

answered Feb 16 '21 at 18:27

Gordon Linoff

1,242,037
58
646
786

1

what is considered as " too long" ? just wondering – eshirvana Feb 16 '21 at 18:27
1

@eshirvana . . . In SQL Server, it is 900 bytes, as explained in the documentation: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-ver15. – Gordon Linoff Feb 16 '21 at 18:33
Thank you! I will give this a shot. – JerrySeinfeld Feb 16 '21 at 22:00

Optimizing queries that search for text

1 Answers1