We run a number of procedures out of LP_CUSTOMER_RANK, and I was just fixing one up. The SQL is much cleaner now, but this has come at the expense of speed: 6ms originally for one with a lot of structural information hard-coded into the procedure, now 100-200ms for one that derives the structural information from tables. Is there a good rule of thumb for how many milliseconds of execution you can get away with in this proc before you start to degrade performance of the client?
Thanks,
Gawain
Hi Gawain,
I’m pretty certain at this point that the problem lies within the LP_CUSTOMER_RANK procedure, since I can’t even get it to affect a record when run manually via SSMS. It’s peculiar for it to have stopped working, because no one has edited it in recent memory. Anyhow, I’m working my way through testing the various sections of our procedure to see where the failure is occurring. Thanks for your advice!
Best,
Stacey
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Gawain Lavers Sent: Wednesday, March 08, 2017 1:26 PM To: Stacey Voigt Subject: Re: [Tessitura Technical Forum] LP_CUSTOMER_RANK
Since LP_CUSTOMER_RANK is designed for custom code, I would suspect that something has changed with the custom code or data structures they rely on that is causing it to fail. Since it is connected to a trigger on pretty much any update to a customer record, LP_CUSTOMER_RANK scripts should generally be designed to fail silently, so you should probably troubleshoot the code in question in isolation to the procedure.
This is assuming you haven't been able to conclude via logs or traces that LP_CUSTOMER_RANK absolutely isn't firing. In that case I'd suggest a TASK ticket.
From: Stacey Voigt <bounce-staceyvoigt1752@tessituranetwork.com> Sent: 3/8/2017 3:20:13 PM
Hello!
I'm hoping someone has wrangled this procedure in the past and can help me troubleshoot - it appears that it's stopped running. In the recent past it ran each time a constituent record was updated - which was less than ideal in many ways - and along the way called another procedure which cleaned up any name and address data entry messes so that the contact information conforms to our consortium data standard.
In doing some related digging this week, I've found that it seems to be neither updating rank nor calling the cleanup function at the expected time. In looking at the trigger on T_CUSTOMER, as well as the code for LP_CUSTOMER_RANK, I don't see anything obvious that would be stopping it.
I should also note that I can't get LP_CUSTOMER_RANK to do what it should (update rank, at a minimum, plus call the other function) even when manually executing it through SSMS.
Any ideas where I should start?
Thanks!
Stacey Voigt
svoigt@granadasb.org
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
Hi Stacy,
Do you have this at the beginning:
IF @customer_no is null
RETURN
EXEC AP_SET_CONTEXT 'LockTrigger', 'Y'
as well as this at the end:
EXEC AP_SET_CONTEXT 'LockTrigger', 'N'
Caryl
Caryl,
Thanks for this! It seems that the issue in our code was that these statements existed, but were in the wrong place. I've put them in the right place and everything seems to be working now.