• Replies 7 replies
  • Subscribers 338 subscribers
  • Views 128 views
  • Users 0 members are here
Related

LP_CUSTOMER_RANK runtime

Zach Domrese
$organization

Hello friends!

I am continuing to add to our ranking procedure and am starting to get concerned with the runtime. Currently sitting at about 60ms. I am hoping to use this thread as a little poll for how long this procedure runs for those of you that have customized it. 

I would love any tips for optimizing as well! 

I've also considered creating a nightly procedure that runs the bulk of the ranking updates and then reserving LP_CUSTOMER_RANK for incremental updates during the day. Has anyone had success with this approach?

I use this to check runtime:

DECLARE @start datetime,
		@end datetime

SET @start = GETDATE()
EXEC [LP_CUSTOMER_RANK] @customer_no=193457
SET @end = GETDATE()

SELECT @start, @end

Thanks, 

  • $organization
    Zach Domrese said:
    I am continuing to add to our ranking procedure and am starting to get concerned with the runtime.

    I was always concerned with this sproc, hating to add anything that might slow down the client. Back in 2015, we moved all of our ranks maintenance to its own nightly job, which I see finished in 2 seconds last night. Even if we wanted to run it more often, I'd tend to keep it out of the Tess client.

  • $organization in reply to Chris Jensen

    I second what said. Our customer rank maintenance happens overnight as well to avoid slowing down the client.

    We do some very quick name & address formatting in LP_CUSTOMER_RANK, but anything beyond that runs in a nightly job.

  • $organization

    We use LP_CUSTOMER_RANK quite a bit: we use it both to compute rank and to apply a few constituencies: these are both key to making TNEW responsive to customer behavior, making sure that customers can received benefits from past purchases even if they log back in a few minutes after the initial purchase.  Performance is an ongoing concern.

    This might help with your speed test, but to be sure times are going to be very different depending on what computations wind up being required for the customer at that moment, and what the current requests for those resources are.

    declare @t datetime = CURRENT_TIMESTAMP;
select top(10000) * from T_CUSTOMER;
select DATEDIFF(MS, @t, CURRENT_TIMESTAMP) as ms;

  • $organization

    We use the custom rank procedure heavily. It needs to happen with each transaction because it allows us to provide comp ticket benefits, pricing changes, and even a work-around for online "subscription exchanges". Right now my procedure run time is 313ms. I wish I could keep it shorter, but the amount of functionality we get from it has been worth the increase in processing time.

  • $organization in reply to Chris Jensen

    Wow that is fast! Are you running it overnight just on records that had updates the previous day or your whole database? If I run the procedure I made to run constituents trough LP_CUSTOMER_RANK for everyone it takes a couple minutes. 

  • $organization in reply to Joshua Peters

    Thanks for this insight! That subscription exchange work around sounds very cool!

  • $organization in reply to Zach Domrese
    Zach Domrese said:
    Are you running it overnight just on records that had updates the previous day or your whole database?

    The nightly sproc looks for qualifying constituents across the whole db, e.g. Volunteers that are active but lack a VOL rank, or Staff that are active but lack a STF rank, and the like. There is nothing the VOL, STF, etc., can do to initiate these themselves, so no need to execute the sproc after every transaction in our case.