Most efficient way to compare strings in SQL Server 2014?
So a couple things:

You are collating both sides of the predicate comparison which can affect your query's cardinality estimate which can severely affect performance. It looks like you're doing this to ensure a case insensitive comparison. You're better off storing the data in tables or a database with that collation specified instead, so you can eliminate the need to do it in your JOIN clause.

Your should insert the data into the table already trimmed to eliminate a function call from your predicates as well, that can also affect the cardinality estimates.

Ensure you have an index on (LastName, FirstName). While it's unlikely to be seeked on in your query (because you're doing a wildcard contains search) even an index scan on those fields might be more performant than nothing.


*4. If you're ok with loosening your comparison logic a little bit (at the risk of the JOIN not matching as many cases) you can do a starts with comparison, and a starts with comparison on the reversed strings of the FirstName and LastName fields instead of the contains search. This will be more efficient because starts with comparisons are SARGable whereas ends width and contains searches are not SARGable, meaning the index will be seekable. If you implement your logic this way that means your predicates would match on &quot;John&quot; and &quot;Johnson&quot;, or &quot;Smith&quot; and &quot;Blacksmith&quot; but would not match on &quot;lack&quot; and &quot;Blacksmith&quot;.