Do UDFs slow performance?

We're trying to determine if a new list of people are already members or on a list telling us not to contact.  So, we're going to compare e-mail addresses, of course but lots of older records don't contain them.  Our member list is enormous.  So, we also are looking to combine strings of a number of different fields, like left(firstname,5) + left(lastname,5) + right(phone,4).  We haven't figured out the best way to do this yet.  Is there a standard?  This can't be a unique situation.
Thanks!

rather than wirting sql using the functions all the time it may be better to either have some tables populated with these partial
field strings and search against them, or to have some computed columns on your main tables and persist and index them...

using functions also limits the usage that the dbms can make of anu indexes you have on the columns in question....

but left(firstnrakle,4)+left(lastname,5)+right(phone,4)    is a standard way to construct as "String"

? do you need to rtrim(ltrim(yourcolumn))   before applying the left/right or have you already dealt with that?

do you also want to consider usage of soundex and difference to detect duplicates?



good luck

Lowfatspread,

The Soundex suggestion could be just the ticket, although I would add the recommendation that a column be added to each table (newly imported and existing) with the Soundex value in it for precisely the index reasons you mentioned.

@Lowfaspread - Thank you.  I won't use the UDF.

re: 'but left(firstnrakle,4)+left(lastname,5)+right(phone,4)    is a standard way to construct as "String"'
I meant is there a standard way that companies generally do these compares.

re:'? do you need to rtrim(ltrim(yourcolumn))   before applying the left/right or have you already dealt with that?'
Yes, I plan to trim - just using a simplified example to clarify my question.

re: 'do you also want to consider usage of soundex and difference to detect duplicates?'
I suggested those to my client but were rejected.  The changes in last names would be from Maiden vs Married names.  First name would be Mike vs Michael.  Those aren't similar.  The one's we'd catch would be Greene vs Green, but people rarely mis spell their own names.  So, we had to throw them out.  - But I thought they were good ideas.

I think I'm just going to go with querying multiple views.  Basically one joined on email, one joined on first = first and last = last and  
(left(middle,1)=left(middle,1) OR right(phone,4)=right(phone,4)).

@8080 Diver
Thanks for your input.  We will have plenty of errors - we just have to do the best that we can.  Essentially, we'll invite a few people that are already members, and a few people that should be invited will get left out.

Will using left() & right() really slow it down?

Oops, forgot to provide example bit of code. ;-)

SELECT N.{list of columns}
FROM NewTable N
LEFT OUTER JOIN
(
 SELECT N.{list of columns}
 FROM NewTable N
 LEFT OUTER JOIN OldTable O
 ON N.EmailAddr = O.EmailAddr
 WHERE O.Identity IS NULL
) O
ON N.FirstName = O.FirstName AND
   N.LastName = O.LastName AND
   N.TelephoneNumber = O.TelephoneNumber 
WHERE O.Identity IS NULL
;

Select allOpen in new window

Thank so much for all suggestions.  Can't alter the tables - long story.  In the end, my client insisted I do it his way - as above:  Basically one joined on email, one joined on first = first and last = last and  
(left(middle,1)=left(middle,1) OR right(phone,4)=right(phone,4)).