December 5, 2016 at 3:07 am
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.
December 5, 2016 at 3:14 am
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.
😎
December 5, 2016 at 4:31 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 5, 2016 at 4:36 am
Thank you. Yet I will need to seek a permission from DBA's to add a new column across all the environments.
December 6, 2016 at 9:42 am
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.
December 6, 2016 at 10:14 am
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.
December 24, 2016 at 5:11 pm
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
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply