Parameterized Query is Magnitudes Slower When Using LIKE

[JamoCA]

I was troubleshooting a MSSQL query that used LIKE. If inline SQL was used, the result was returned within 400-550 ms. QB (or even using QueryExecute w/bind parameters) was taking ~27,000 ms by comparison.

While researching the issue, I came across a SQL resource with good advice:

• https://use-the-index-luke.com/sql/where-clause/bind-parameters
  The performance of LIKE queries can suffer from bind parameters.
• https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning
  LIKE expressions that start with wildcards force the database to perform a full table scan.
  One recommendation is to use a full-text index, but “only one full-text index can be created on each table so if you have more than one column you need to be indexed the columns have to be moved to separate tables”, so this may not be something that can easily be done.
  Specifying the search term without bind parameter is the most obvious solution, but that increases the optimization overhead and opens an SQL injection vulnerability.
• https://use-the-index-luke.com/sql/where-clause/obfuscation/concatenation
  If you know that there is always a leading wild card, you can obfuscate the LIKE condition intentionally so that the optimizer can no longer consider the index.
  It is enough to append an empty string to the column. This is, however, an option of last resort. Only do it when absolutely necessary.

I ended up changing this:

DECLARE @company NVARCHAR(100);
SET @company = '%My Partial Company Name%';
SELECT ID, Company FROM Clients WHERE Company LIKE @company;

to something like this:

DECLARE @company NVARCHAR(100);
SET @company = '%My Partial Company Name%';
SELECT ID, Company FROM Clients WHERE COALESCE(Company,'') LIKE @company;
--- or SELECT ID, Company FROM Clients WHERE Company+'' LIKE @company;

and the query performance was increased where the sql execution time was reduced from 26,777ms to 62ms! (Even faster than Inline SQL; which was 438ms).

Here's what it looks like using QB syntax.

q.whereRaw( "Company+'' LIKE ?", ["%My Partial Company Name%"]);

NOTE: I'm posting this in case developers blame QB for the slowness and return to writing unsafe, inline SQL (without data binding.) Would you be interested in automatically parsing the LIKE value, identify the "%" prefix and automatically generate this "LIKE obfuscation" hack. (I'm not sure if this should be the default approach or not, but it was extremely beneficial for our project.)