question

Bhupendra99 avatar image
Bhupendra99 asked

How to fast query serching record where name like 'Rock%'

We had an table let's say employee with only one column name having repetative values Now if I want to search for records that starts with and ending with name say Rock in two diffrent query something as select name from employee where name like 'Rock%'--name starting with Rock select name from employee where name like '%Rock'--name ending with Rock It's obvious that my first query will run faster now my question is how can I make second query faster without changing the execution time of first query
like
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
If the Speed is really important then: For the first case build index on the search field. As LIKE 'Roc%' is compatible with index. The second query with '%Rock' will cause full table scan as it is not compatible with indexes. But it is possible to create a persisted calculated `ReversedField` column, which will `REVERSE(Field)`. Then build index on the `ReversedField` and for searching you will use WHERE ReversedField LIKE REVERSE('%Rock') This will be compatible with the index on the reversed column and the speed of searching will be the same as in case of the first query.
7 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

A persisted computed column is one that is computed on INSERT/UPDATE-time and then stored as a value in the table. The syntax is like this: ALTER TABLE tableName ADD columnName AS reverse(otherColumnName) PERSISTED Then you can index it like any other column. If you don't use the keyword PERSISTED, the column value will be computed every time it is accessed. For a computed column with a function call in the formula, I would strongly recommend persisting it, for performance reasons.
3 Likes 3 ·
You will have the reverse value kcoRdeB and the WHERE will be (after reversing %rock) WHERE 'kcoRdeB' LIKE 'kcoR%'. So that will work fine. One thing though: When making lookups to other tables - aka joining on a like-expression the function call will not be very efficient. And persisting a reversed column value will have an impact on insert- and update performance. But given this scenario, a persisted, indexed reversed column is probably the best option.
2 Likes 2 ·
Related to the output.. You will use the Reversed Persisted column in the WHERE Clause for searching. TO return data, you will use the original Non Reversed column. The reversed persisted column is to improve the query performance by building index on it and will be very useful in case of heavy querying when using the LIKE '%Rock'.
1 Like 1 ·
As @Magnus mentioned you add new persisted column to your table ALTER TABLE YourTable ADD ReversedColumn AS REVERSE(NonReversedColumn) PERSISTED; CREATE INDEX IX_YourTable_ReversedColumn ON YourTable(ReversedColum) Once done, you will have for queries **`WHERE ReversedColumn LIKE REVERSE('%Rock')`** The same performance as for queries using **`WHERE NonReversedColumn LIKE 'Rock%'`**.
1 Like 1 ·
@Pavel Pawlowski - A query on the answer: Wont REVERSE of 'Rock' give 'kcoR' as output? So if the search is for 'BedRock' for example and the SELECT is prepared for LIKE '%Rock' wont reversing put BedRock itself as kcoRdeB. How will then a search be successful?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.