Posted by Erland Sommarskog on 10/26/05 00:57

Ken Post (nntp.post@gmail.com) writes:
> Thanks for the reply Erland!
>
> The function can't be optimal the way I've got it, but it works. It's
> not exactly just a plain lookup, it's numbering address rows. Still,
> I'm VERY eager to understand how I could do this with a join for better
> performance. Can you explain?

Below is an attempt to a rewrite, which may flat out wrong. (I did not
understand the ORDER BY, so I simply ignored those. :-)

Since yours is a bit complicated, it may warrant a function, as long as
performance is decent. But I recently leard that there are people who
do things like:

SELECT OrderId, dbo.GetCustomerName(CustomerID), ...
FROM ...

and the UDF is a plain SELECT. That is very unnecessary.


SELECT Poss.*,
MIN(CASE WHEN N.LineNumber WHEN 1 THEN UDF.FullAddr) AS FullAddr1,
MIN(CASE WHEN N.LineNumber WHEN 2 THEN UDF.FullAddr) AS FullAddr2
dbo.udf_ReturnAddress(FoundPersonID, 1) AS FullAddr1,
dbo.udf_ReturnAddress(FoundPersonID, 2) AS FullAddr2
FROM dbo.udf_ReturnPossibleDupsForAPerson(@PersonID) Poss
JOIN (SELECT Adr.PersonID, N.LineNumber,
FullAddr = Adr.Address1 +
CASE WHEN len(Adr.Address2) > 0
THEN ', ' + Adr.Address2
ELSE ''
END +
CASE WHEN len(Adr.Address3) > 0
THEN ', ' + Adr.Address3
ELSE ''
END + Adr.City
FROM dbo.ppl_addresses Adr
JOIN (SELECT COUNT(*) LineNumber, a.PersonID, a.AddressID
FROM ppl_Addresses A
JOIN ppl_Addresses B ON A.AddressID >= B.AddressID
AND A.PersonID = B.PersonID
GROUP BY A.PersonID, A.AddressID) N
ON Adr.PersonID = N.PersonID
AND Adr.AddressID = N.AddressID
WHERE N.LineNumber IN (1, 2)) AS UDF
ON UDF.PersonID = Poss.FoundPersonID
GROUP BY Poss.PersonID, Poss.col1, Poss.col2, ...



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]