How can I modify this query in SQL Server?

Question

I'm trying to modify this query, using levhenstein. My problem its that I can add my condition in inner join query, not in where query.

This is my actual query:

SELECT     
    registrosImportados.id, TVC.NOMBRETVC, 
    VIACU.NOMBREVIACU, CUT.GLOSA, 
    registrosImportados.direccion,
    dbo.fn_LevenshteinDistance(registrosImportados.direccion, VIAIU.NOMBREVIAIU, 5)
FROM         
    ACOPIOCI 
INNER JOIN
    TVC ON ACOPIOCI.IDTVC = TVC.IDTVC 
INNER JOIN
    VIACU ON ACOPIOCI.IDVIACU = VIACU.IDVIACU 
INNER JOIN
    VIAIU ON ACOPIOCI.IDVIAIU = VIAIU.IDVIAIU 
INNER JOIN
    CUT ON ACOPIOCI.IDCUT = CUT.IDCUT 
INNER JOIN
    registrosImportados ON CUT.CUT = registrosImportados.cut
WHERE
    dbo.fn_LevenshteinDistance(registrosImportados.direccion, VIAIU.NOMBREVIAIU, 5) = (SELECT MIN(M.DISTANCIA)
                     FROM 
                         (SELECT dbo.fn_LevenshteinDistance(registrosImportados.direccion, VIAIU.NOMBREVIAIU, 5) AS DISTANCIA
                          FROM ACOPIOCI 
                          INNER JOIN VIAIU ON ACOPIOCI.IDVIAIU = VIAIU.IDVIAIU 
                          WHERE ACOPIOCI.IDCUT = CUT.IDCUT) AS M
    )
    AND ACOPIOCI.IDCUT = CUT.IDCUT

And this is what I want to do:

SELECT      
    VIACU.NOMBREVIACU, registrosImportados.DIRECCION,
    dbo.fn_LevenshteinDistance(registrosImportados.direccion,  VIAIU.NOMBREVIAIU, 2) DIST
FROM         
    ACOPIOCI 
INNER JOIN
    TVC ON ACOPIOCI.IDTVC = TVC.IDTVC 
INNER JOIN
    VIACU ON ACOPIOCI.IDVIACU = VIACU.IDVIACU 
INNER JOIN
    VIAIU ON ACOPIOCI.IDVIAIU = VIAIU.IDVIAIU 
INNER JOIN
    CUT ON ACOPIOCI.IDCUT = CUT.IDCUT 
INNER JOIN
    registrosImportados ON CUT.CUT = registrosImportados.cut 
INNER JOIN
    (SELECT  
        MIN(dbo.fn_LevenshteinDistance(registrosImportados.direccion, VIAIU.NOMBREVIAIU, 2)
    ) AS MINDIST) M ON registrosImportados.DIST = M.MINDIST

But it doesn't work. My idea is to get performance in my query.

Answer 1

The best solution to this problem is to store the Levenshtein distance computation results in a table, possibly as a computed column, so that updates are automatic, such that you can put an index on that value. Until you do this, you're gonna have performance issues.

That said, we can at least make some improvements to the query. The distance calculation is currently used 3 times in your query: for the selection list, in the where clause, and to compute the minimum distance. I expect Sql Server is smart enough to use the same calculation for both the SELECT and WHERE uses (You should verify this by checking the execution plan), but I doubt it's able to do that for the calculating the minimum distance.

This means the query is effectively executed twice: once for the base data, and once to compute the minimum distance; almost all of the work from the base data query has to be duplicated. We can make the query faster by pulling the unfiltered results from the main part of the query (that include the levenshtein distance calculation) into a temp table or CTE (the best option depends mainly on the size of the data), and then selecting from that table/CTE. This will help Sql Server know how to avoid computing the distance twice for every possible result record.

For this example, I'll use the CTE option:

With Data As 
(
   SELECT     
      r.id, b.NOMBRETVC, c.NOMBREVIACU,
      CUT.GLOSA, r.direccion,
      dbo.fn_LevenshteinDistance(r.direccion,i.NOMBREVIAIU,2) As Distance
   FROM ACOPIOCI a
   INNER JOIN TVC b ON a.IDTVC = b.IDTVC 
   INNER JOIN VIACU c ON a.IDVIACU = c.IDVIACU 
   INNER JOIN VIAIU i ON a.IDVIAIU = i.IDVIAIU 
   INNER JOIN CUT ON a.IDCUT = CUT.IDCUT 
   INNER JOIN registrosImportados r ON CUT.CUT = r.cut
)
SELECT id, NOMBRETVC, NOMBREVIACU
FROM Data
WHERE Distance = (SELECT MIN(Distance) FROM DATA)

If you have a lot of rows in the CTE (more than you have free memory), you'll do better using a temp table or table variable instead.