4

I have a very basic table:

CREATE TABLE [obj_local] (
    [obj_id]     INT             NOT NULL,
    [value]      NVARCHAR (1000) NOT NULL
);

This table stores a lot of data, and I need to search the value column for a sequence of characters:

SELECT [obj_id] FROM [obj_local] WHERE [value] LIKE '%{substring}%'

This is extremely slow. My understanding is that an index won't help me due to the wildcards on either side, and a full text index also won't help me, because I'm not searching for tokens(words).

Is there anything I can do to optimize this search?

  • is the data you are looking for separated by any particular character such as a space or a comma? Or are you looking in a big long string for several characters? – Max Vernon Aug 6 '15 at 21:08
  • I'm looking in a big long string for a sequence of characters. The actual content of the value column is usually relatively small (10-20 characters), if that helps. – Collin Dauphinee Aug 6 '15 at 21:09
  • Checkout this stack overflow post link. Another question that might be relevant is link. – Erik Aug 6 '15 at 21:10
  • 1
    Other than full-text search, which relies on word separation, no, there is no realistically practical way to optimize that query. What you could do is if you have a very common substring that you search for frequently, you could make a computed column based on the presence or absence of that substring, and pay the cost of search at write time instead of query time. – Aaron Bertrand Aug 6 '15 at 21:30
  • Since all signs say search performance will be dismal in Sql Server have you considered storing this data outside of Sql Server in a database optimized for this problem? Sometimes the solution isn't to get a bigger fancier hammer, but to reach for an ordinary screw driver. There are several full text specific databases out there that may be orders of magnitude faster. I've heard good things about Lucene and its brother Elasticsearch, but I've never used them personally. – Erik Aug 7 '15 at 3:09
5

If you know the exact string, then using a binary collation for your search can help.

WHERE [value] LIKE '%{substring}%' COLLATE Latin1_General_BIN

, because it won't have to do case conversions and the like. This can make it a few times faster, but not lightning fast.

Another option is to consider blowing up your table and indexing that - using an indexed view over a john between a table of numbers and your table, leveraging the substring function.

For example, if you have a table of at least 1000 numbers, you could make a row for each character. Yes, it'll cost space, but it will quickly let you find every "H" in there.

Problem is - it's still not quite what you need, because there will be a lot of "H"s. Better might be to grab three-letter combinations. So if you're looking for 'Hotel', you know that either "Hot", "ote" or "tel" must be in the three-letter combo table. You could make an inline table-valued function to handle this. Naturally, when you search for "Hot", the next block for that obj_id must be like 'el%', and so on, plus you should check that your main table still successfully satisfies the main predicate.

It's an idea... if you can put up with the complexity of working around it like this.

  • Some support for binary collations can be found in this technet article. Based on the final benchmark in the article this might be the ticket. The other options @RobFarley mentions sound like a hot mess to me, but Rob also clued me into the binary collation bit so he clearly knows tricks I don't. :) – Erik Aug 7 '15 at 3:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.