How to improve performance, when using LIKE (or any other alternative?)

Question

How to improve performance, when using LIKE (or any other alternative?)

BOR15K

SSCertifiable

Points: 5894
More actions
December 5, 2016 at 3:07 am

#401163

I have a table of several millions of records for flight ticket. One of the indices is Flight Ticket, so when user searches for ticket 1234567 or 123456% all is perfect. Now there is a requirement to also allow %234567 search, which obviously runs for too long (10 - 15 minutes). Is there any suggestion / approach / alternative / documentation what should be done in those cases, please?
Much obliged.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

BOR15K (12/5/2016)
I have a table of several millions of records for flight ticket. One of the indices is Flight Ticket, so when user searches for ticket 1234567 or 123456% all is perfect. Now there is a requirement to also allow %234567 search, which obviously runs for too long (10 - 15 minutes). Is there any suggestion / approach / alternative / documentation what should be done in those cases, please?
Much obliged.

One option is to have a reverse copy column, the search would then be "765432%", somewhat simpler than full text indexing.

😎

Phil Parkin SSC Guru Points: 246987 More actions · Answer 2

Eirikur Eiriksson (12/5/2016)
BOR15K (12/5/2016)
I have a table of several millions of records for flight ticket. One of the indices is Flight Ticket, so when user searches for ticket 1234567 or 123456% all is perfect. Now there is a requirement to also allow %234567 search, which obviously runs for too long (10 - 15 minutes). Is there any suggestion / approach / alternative / documentation what should be done in those cases, please?
Much obliged.
One option is to have a reverse copy column, the search would then be "765432%", somewhat simpler than full text indexing.
😎

Sounds good. Make it computed, persisted and indexed and it should work very well.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

BOR15K SSCertifiable Points: 5894 More actions · Answer 3

Thank you. Yet I will need to seek a permission from DBA's to add a new column across all the environments.

MadAdmin SSChampion Points: 11261 More actions · Answer 4

Have a look at the disparity in reads when using a variable or a literal, and option recompile.

You could use this trick to make it quicker.

set statistics io on

use AdventureWorks2016CTP3

GO

declare @var nvarchar(20)=N'%ith'

select * from person.person where lastname like @var

--Table 'Person'. Scan count 1, logical reads 3895

select * from person.person where lastname like @var option(recompile)

--Table 'Person'. Scan count 1, logical reads 441

select * from person.person where lastname like N'%ith'

--Table 'Person'. Scan count 1, logical reads 441

But Eirikur's method is probably the best for a highly transactional environment.

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

Eirikur's idea is a really good one. The only thing I'd add is that if you're going to define a column to store the reverse of the string, I'd do it as a persisted computed column. Then, when a row is INSERTed or UPDATEd, you won't have to worry about the new column - it'll be populated for you.

If you intend to use it in WHERE clauses, you could add a nonclustered index on it to cover your search queries. If it's the leading column in the index, you should be able to get index seeks.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 6

BOR15K (12/5/2016)
Thank you. Yet I will need to seek a permission from DBA's to add a new column across all the environments.

Write a script to do it and submit the script to them. They'll like you a whole lot more, especially if the script is properly documented and provides success/failure notifications and will survive and accidental rerun.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)