Hello all. I've been working on preparing for our v11 customization migration, and part of that is replacing joins to T_PHONE with while-loops or cursors to allow for looping to call AP_GET_PHONES for each customer_no in a data set. It looks like this approach could be pretty resource-intensive, so I developed a local table-value function to do for phone data what FT_GET_ADDRESS and FT_GET_EADDRESS (another part of the preparation being to replace joins to T_ADDRESS and T_EADDRESS with those functions) do for those kinds of data.
From my initial testing, it's much more efficient to call the function as part of a join than to go the RBAR approach (like minutes vs. hours).
What I'm wondering, though, is why this wasn't recommended in the first place. Am I missing something? We're just about to start testing out my new function, and I'm curious if anyone has tried this and found a reason why it doesn't work. Because the loop-to-call-a-stored-procedure approach is so different from the table-value-function approach, I'm second-guessing and thinking it was intentionally made different because of some inherent quality I'm overlooking.
Thanks in advance!
It would be interesting to see what you came up with as I would have guessed that a join to table function that did everything that AP_GET_PHONES does, including calling the local procedure, LP_GET_PHONES, would not be much faster than calling AP_GET_PHONES in a loop. Because unless you can create an inline function (in which there is only one SQL statement) a lot of looping happens anyway. I would be curious to see your results.
Thank you for replying, Chuck. I sent you an email with the code (wasn't sure if posting it here as a wall of text was the right thing to do), and I look forward to hearing what you think.
Nathan D. Wigham
Pittsburgh Cultural Trust Consortium
Chuck was kind enough to look at my function and agreed that it could cut down on execution time if you can combine code from the LP_GET_PHONES and AP_GET_PHONES procs. The ability to customize the local proc is what prevents a universal solution - any local customizations would have to be duplicated in your table-value function.
Thanks again, Chuck!
This is great timing for us, as I've been tinkering with the same thing. Did you end up making a call to lp_get_phones? Or did you simply put any custom logic from that into your function?
It turns out I was lucky in that we don't have much in the way of customization in LP_GET_PHONES, so my version is probably much simpler than it would be for another organization. But I would incorporate customizations into the joins (see my example below), and use Coalesce to prioritize which results are used.
Normal 0 false false false EN-US X-NONE X-NONE
USE [impresario]
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION dbo.LFT_GET_PHONES
(@customer_no INT,
@address_no INT = NULL,
@mail_dt DATETIME = NULL
)
RETURNS TABLE
AS
/*
Created 4/11/2012 NWigham - Originally created for the v11 customization migration process, to take the place of Cursors or while-loops calling AP_GET_PHONES.
This function takes customer_no and (optionally) address_no and mail_dt.
It returns a table containing customer_no, address_no (as passed in, or as provided by FT_GET_ADDRESS),day_phone, eve_phone, and fax_phone.
Logic is adapted from the stored procedures LP_GET_PHONES and AP_GET_PHONES.
*/
RETURN (
SELECT customer_no = @customer_no,
address_no = COALESCE(@address_no,a.address_no,h.address_no),
day_phone = COALESCE(d1.day_phone,d2.day_phone),
eve_phone = COALESCE(e1.eve_phone,e2.eve_phone),
fax_phone = COALESCE(f1.fax_phone,f2.fax_phone)
FROM dbo.FT_GET_ADDRESS(@mail_dt, NULL, 'N', @customer_no) a
--to get a "default" address if none is provided
--This join replicates a step in AP_GET_PHONES, providing an alternate address_no in case one isn't provided or found above
LEFT JOIN (SELECT DISTINCT a.customer_no, a.address_no
FROM dbo.V_CUSTOMER_WITH_HOUSEHOLD h (NOLOCK)
JOIN dbo.T_ADDRESS a (NOLOCK) ON a.customer_no = h.expanded_customer_no AND a.primary_ind = 'Y') AS h ON h.customer_no = @customer_no
--Next 3 joins replicate LP_GET_PHONES proc
LEFT JOIN (SELECT customer_no, address_no, MAX(ISNULL(phone,' ')) AS day_phone
FROM dbo.VS_PHONE (NOLOCK)
WHERE [type] = 1
AND phone > ' '
GROUP BY customer_no, address_no) AS d1 ON d1.customer_no = @customer_no AND d1.address_no = COALESCE(@address_no,a.address_no,h.address_no)
LEFT JOIN (SELECT customer_no, address_no, ISNULL(phone,' ') AS eve_phone
WHERE [type] = 2
AND phone > ' ') AS e1 ON e1.customer_no = @customer_no AND e1.address_no = COALESCE(@address_no,a.address_no,h.address_no)
LEFT JOIN (SELECT customer_no, address_no, ISNULL(phone,' ') AS fax_phone
WHERE [type] = 3
AND phone > ' ') AS f1 ON f1.customer_no = @customer_no AND f1.address_no = COALESCE(@address_no,a.address_no,h.address_no)
--Next 3 joins replicate AP_GET_PHONES proc, after calling LP_GET_PHONES
WHERE [type] = 7
GROUP BY customer_no, address_no) AS d2 ON d2.customer_no = @customer_no
FROM dbo.T_PHONE (NOLOCK)
AND phone > ' ') AS e2 ON e2.customer_no = @customer_no AND e2.address_no = COALESCE(@address_no,a.address_no,h.address_no)
AND phone > ' ') AS f2 ON f2.customer_no = @customer_no AND f2.address_no = COALESCE(@address_no,a.address_no,h.address_no)
In his response, Chuck suggested calling FT_GET_PRIMARY_ADDRESS() to replace the subquery that looks at V_CUSTOMER_WITH_HOUSEHOLD (which I took from AP_GET_PHONES), and I plan to do that.
Hey, great work Nathan. Thanks for sharing!
I never got the max() thing. I guess there's an idea is that a business phone isn't necessarily connected to a customer's address, so I could see why that one might need to be treated differently, But it seems to me to produce some odd results.