We have a number of jobs that perform matches across different tables. The initial match is based on the forename and surname of the person in question being equal. If the forename and surname is the same and, a record is created in another table and further matching is performed to give a final match rating.
We have just purchased a database containing million of name variations. These are in two tables (one for forenames and one for surnames) with the following columns:
" Source_Name This contains a list of Names that you can search on (E.g. Steve)
" Name_Variation This list the variation of the sources name. Each variation is on a new row. (I.e. there may be 2 rows with the source name Steve. One with the variation of Stephen and the other with the variation Steven)
" Score This is a rating system with 100 being a close match and 75 being a loose match.
I am having trouble getting the matching script to return results in an efficient manner. I believe that this is caused by the cross join that I have used in the below script. Is there a more efficient way of doing it?
FROM PERSON P
Cross JOIN C6..PERSON_SUBSET PER
WHERE P.Forename <> ''
P.Surname <> ''
And (P.Forename = PER.EnglishForename Or PER.PERSONID in (
Where EnglishForename In
Where [Source_Name] = P.Forename and Score >= 95)))
AND (P.Surname = PER.EnglishSurname OR PER.PERSONID in (
Where EnglishSurname in
Where [Source_Name] = P.Surname and Score >= 95)))