Hi All,
I hoping someone can help me with an issue I'm having
I'm trying to design a fuzzy search feature for a C# application that uses MS Access as the DB. I am searching on a list of horse names, there are 130,000 horse records in the database. I've tried lots of different algorithms but the one that seems to suit my need best is levenshtein distance. I'm really just trying to account for spelling mistakes when the user tries to find a horse. I've added the VBA to the database as a function, which I then use in my SQL. I can run the function in the SELECT part of the statement without any issues, 'SELECT name, levenshtein(a,b) FROM Horses' returns the whole table in under 2 seconds. But as soon as I put it in the WHERE clause the query takes 5+ minutes, 'SELECT name, FROM Horses WHERE levenshtein(a,b) <= 2'.
Has anyone experienced the massive time difference when including a function in the WHERE clause?
Is there a better solution for my fuzzy search?
Any help is appreciated.
Thanks
Tim
I hoping someone can help me with an issue I'm having
I'm trying to design a fuzzy search feature for a C# application that uses MS Access as the DB. I am searching on a list of horse names, there are 130,000 horse records in the database. I've tried lots of different algorithms but the one that seems to suit my need best is levenshtein distance. I'm really just trying to account for spelling mistakes when the user tries to find a horse. I've added the VBA to the database as a function, which I then use in my SQL. I can run the function in the SELECT part of the statement without any issues, 'SELECT name, levenshtein(a,b) FROM Horses' returns the whole table in under 2 seconds. But as soon as I put it in the WHERE clause the query takes 5+ minutes, 'SELECT name, FROM Horses WHERE levenshtein(a,b) <= 2'.
Has anyone experienced the massive time difference when including a function in the WHERE clause?
Is there a better solution for my fuzzy search?
Any help is appreciated.
Thanks
Tim