Fast matching of input string with strings in db

Question

Fast matching of input string with strings in db

entity-framework-core full-text-search sql-server

Question

Given an algorithm of strings matching which works with certain strings (e.g. "123456789") and string patterns (e.g. "1*******9"). String patterns are not any kind of regexp or SQL LIKE pattern - they only provide "*" placeholder which means "a single digit or letter".

So, the algorithm will treat these values as "equal":

12ABCDE89
12A***E89
**A****8*
*********

The data is stored in the relational database (MS SQL Server) and .net core app addressed it via EntityFramework Core. The required scenario is to obtain 500 input strings (either certain or a pattern) and to find matched rows in the database (in the table containing 1 million of rows).

First I implemented it using LIKE pattern matching (first I transformed input strings to the LIKE pattern and then built a predicate for WHERE clause), but tests showed that it has unacceptable performance.

Can I implement this task using FULL-TEXT SEARCH feature of MSSQL? What will the predicate look like in this case? Any other ideas on the implementation?

Andrey Pesoshin

View more on Stack Overflow

user2700840 · Accepted Answer · 12/22/2018 11:20:58 AM

You can try CLR user-defined function approach (example). But you don't need to use SQL queries at all. Just compare 2 strings using your algorithm. Such approach theoretically should be faster

  [SqlFunction(
      DataAccess = DataAccessKind.None,
      SystemDataAccess = SystemDataAccessKind.None,
      IsPrecise = true,
      IsDeterministic = true)
  ]

  // Have to be public and static
  public static bool CustomIsEqualTo(string baseString, string stringToCompare)
  {
      return true;   
  }

Fast matching of input string with strings in db

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related