Speeding up a query

[webbeacon]

Hello again...

This expression is working, but is very slow. I suspect it's because the SQL is peforming the DamerauLevenshtein function twice for each record, and the function is pretty intense to begin with.

I'm wondering if incorporating the MIN() from the SQL right into the DamerauLevenshtein function might speed things up, possibly using VBA syntax instead of SQL. As in, identify the best match right in the function rather than retrieving a list of possible matches and then running the same function again to determine the MIN and pick the right one using a JOIN. It would mean passing the maximum threshhold for error (in this case, "<3") to the function instead of declaring it in the SQL, then using that as a basis to create an array of results and pick the best one, then send it back to the SQL.

I hope that makes sense :/

Anyway here's the SQL:

SELECT t1.*
FROM (
 
SELECT TCSDBOWNER_EMP.ID AS EMP_ID, TCSDBOWNER_EMP.LAST_NAME AS EMP_LNAME, TCSDBOWNER_EMP.FIRST_NAME AS EMP_FNAME, q_insCrosstab.[SK Insurance], TCSDBOWNER_EMP.EMAIL_ADR AS EMP_EMAIL, TCSDBOWNER_EMP.ACTIVE_FLAG AS ACTIVE, SK_Ins.[Full_Name], DamerauLevenshtein(q_insCrosstab.[NAME],SK_Ins.[Full_Name]) AS DamLev FROM SK_Ins, TCSDBOWNER_EMP,q_insCrosstab WHERE DamerauLevenshtein(q_insCrosstab.[NAME],SK_Ins.[Full_Name]) <3 AND  TCSDBOWNER_EMP.ACTIVE_FLAG="T" ORDER BY TCSDBOWNER_EMP.LAST_NAME, TCSDBOWNER_EMP.FIRST_NAME
 
)  AS t1 
 
RIGHT JOIN (
 
SELECT EMP_ID, MIN(DamLev) AS MinDamLev FROM (
 
SELECT TCSDBOWNER_EMP.ID AS EMP_ID, TCSDBOWNER_EMP.LAST_NAME AS EMP_LNAME, TCSDBOWNER_EMP.FIRST_NAME AS EMP_FNAME, q_insCrosstab.[SK Insurance], TCSDBOWNER_EMP.EMAIL_ADR AS EMP_EMAIL, TCSDBOWNER_EMP.ACTIVE_FLAG AS ACTIVE, SK_Ins.[Full_Name], DamerauLevenshtein(q_insCrosstab.[NAME],SK_Ins.[Full_Name]) AS DamLev FROM SK_Ins, TCSDBOWNER_EMP,q_insCrosstab WHERE DamerauLevenshtein(q_insCrosstab.[NAME],SK_Ins.[Full_Name]) <3 AND  TCSDBOWNER_EMP.ACTIVE_FLAG="T" ORDER BY TCSDBOWNER_EMP.LAST_NAME, TCSDBOWNER_EMP.FIRST_NAME
 
)  AS x 
 
GROUP BY EMP_ID
 
)  AS t2 ON (t1.EMP_ID = t2.EMP_ID) AND (t1.DamLev = t2.MinDamLev);

and the VBA:

Function DamerauLevenshtein(str1 As String, str2 As String, Optional intSize As Integer = 256)
 
     Dim intTotalLen As Integer, arrDistance, intLen1 As Integer, intLen2 As Integer, i As Integer, j As Integer, arrStr1, arrStr2, arrDA, intMini As Integer
     Dim intDB As Integer, intI1 As Integer, intJ1 As Integer, intD As Integer
 
    'adding basAlphNum() to strip spaces and non-alpha chars
     str1 = basAlphNum(str1)
     str2 = basAlphNum(str2)
    'original code for DamerauLevenshtein() follows
 
     str1 = UCase(str1)
     str2 = UCase(str2)
     intLen1 = Len(str1)
     intLen2 = Len(str2)
     intTotalLen = intLen1 + intLen2
     ReDim arrStr1(intLen1)
     ReDim arrStr2(intLen2)
     ReDim arrDA(intSize)
     ReDim arrDistance(intLen1 + 2, intLen2 + 2)
     arrDistance(0, 0) = intTotalLen
 
     For i = 0 To intSize - 1
         arrDA(i) = 0
     Next
 
     For i = 0 To intLen1
         arrDistance(i + 1, 1) = i
         arrDistance(i + 1, 0) = intTotalLen
     Next
 
     For i = 1 To intLen1
         arrStr1(i - 1) = Asc(Mid(str1, i, 1))
     Next
 
     For j = 0 To intLen2
         arrDistance(1, j + 1) = j
         arrDistance(0, j + 1) = intTotalLen
     Next
 
     For j = 1 To intLen2
         arrStr2(j - 1) = Asc(Mid(str2, j, 1))
     Next
 
     For i = 1 To intLen1
         intDB = 0
 
         For j = 1 To intLen2
             intI1 = arrDA(arrStr2(j - 1))
             intJ1 = intDB
 
             If arrStr1(i - 1) = arrStr2(j - 1) Then
                 intD = 0
             Else
                 intD = 1
             End If
 
             If intD = 0 Then intDB = j
 
             intMini = arrDistance(i, j) + intD
             If intMini > arrDistance(i + 1, j) + 1 Then intMini = arrDistance(i + 1, j) + 1
             If intMini > arrDistance(i, j + 1) + 1 Then intMini = arrDistance(i, j + 1) + 1
             If intMini > arrDistance(intI1, intJ1) + i - intI1 + j - intJ1 - 1 Then intMini = arrDistance(intI1, intJ1) + i - intI1 + j - intJ1 - 1
 
             arrDistance(i + 1, j + 1) = intMini
         Next
 
         arrDA(arrStr1(i - 1)) = i
     Next
 
     DamerauLevenshtein = arrDistance(intLen1 + 1, intLen2 + 1)
 End Function

Edit: as always, I appreciate any feedback you have on how to improve my question or communicate more effectively. And thank you for any suggestions you might have on how to speed this up :)

[Rabbit]

It may be quicker if you write out the results of the query to a temp table and then do the min query on that.

[webbeacon]

That's a great idea, thank you!