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,
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.
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.
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.