Matching entities in SQL Server to replace iterative Python script
Without knowing exactly what your old code was it is hard to pinpoint where the slowdown came from. That being said, here are some things you're doing in your query that are going to make it slower:


Using a UDF prohibits parallel plans. You could throw 1000 CPUs at this problem and it would still only use one of them. If computing this is too complex to do inline, then use an inline table-valued function instead, which enables parallelism.
JOINing on functions (UDF or otherwise) performs really poorly. SQL Server has no cardinality estimate for LEFT(input.name, 3), but it does for input.name. If at all possible find another way to do this join (a suggestion is below). Similarly, filtering in a WHERE clause has the same restrictions.


To address this, I would likely change your join to just be on RefCode - this gets you a good hash match (probably), but will increase your granularity. Then, make your edit_distance function table valued, like so

SELECT &lt;&lt;stuff&gt;&gt;
  FROM dbo.Sample input
    INNER JOIN dbo.Company target_db
      ON input.ReFcode = target_db.Refcode
    CROSS APPLY dbo.edit_distance( input.name, target_db.CompanyName ) levenshtein


Then, inside of dbo.edit_distance I would include the LEFT, SOUNDEX and LEN calculations (LEN calc would also be better if you did LEN( input.name ) - LEN( targetdb.CompanyName ) BETWEEN -7 AND 7).

If levenshtein returns some score, then you could do something like this to only get the best score per input name. Alternatively if you want them all, then have it return everything and do whatever calculations the consuming app needs.

SELECT name,
       CompanyName,
       Country,
       RefCode,
       levenshtein_score
  FROM ( SELECT input.name,
                target_db.CompanyName,
                input.Country,
                input.ReFcode,
                levenshtein.levenshtein_score,
                ROW_NUMBER() OVER ( PARTITION BY input.name
                                    ORDER BY levenshtein.levenshtein_score ASC ) RowNumber
           FROM dbo.Sample input
             INNER JOIN dbo.Company target_db
               ON input.ReFcode = target_db.Refcode
             CROSS APPLY dbo.edit_distance( input.name, target_db.CompanyName ) levenshtein ) ComputedLevenshteinScores
  WHERE ComputedLevenshteinScores.RowNumber = 1
  ORDER BY levenshtein ASC;