0

I do have the stored procedure to calculate some facts (say usp_calculate). It fills the cache-like table. The part of the table (determined by the arguments of the procedure) table must be recalculated every 20 minutes. Basically, the usp_calculate returns early if cached data is fresh, or it spends say a minute to calculate... and returns after that.

The usp_calculate is shared by more outer procedures that needs the data. How should I prevent starting the time-consuming part of the procedure if it was already started by some other process? How can I implement a kind of signaling and waiting for the result instead of starting the calculation again?

Context: I do have an SQL stored procedure named say usp_products. It finally performs a SELECT that returns the rows for a product code, a product name, and calculated information -- special price for a customer, and for the storage location. There is a lot of combinations (customers, price lists, other conditions) that prevent to precalculate the information by a separate process. It must be calculated on-demand, for the specific combination.

The third party database that is the source of the information is not designed for detecting changes. Anyway, the time condition (not older than 20 minutes) is considered a good enough to consider the data "fresh".

pepr
  • 20,112
  • 15
  • 76
  • 139
  • This feels like a Job as described here https://stackoverflow.com/q/865042/125981 – Mark Schultheiss Apr 15 '20 at 14:15
  • Note you can also start the job from a stored procedure https://stackoverflow.com/q/15906923/125981 – Mark Schultheiss Apr 15 '20 at 14:18
  • @MarkSchultheiss: This cannot be started as a job. There are specific conditions that requires to start it at the time of the demand. The job would have to be named to reflect the specific conditions. – pepr Apr 15 '20 at 14:20
  • See that second example on how to pass a parameter, that could be combined with a table that acts as a "queue" with a flag for running and last run time, values needed/ran with, things like that – Mark Schultheiss Apr 15 '20 at 14:21

1 Answers1

2

The building block for this is probably going to be application locks.

You can obtain an exclusive application lock using sp_getapplock. You can then determine if the data is "fresh enough" either using freshness information inherently contained in that data or a separate table that you use for tracking this.

At this point, if necessary, you refresh the data and update the freshness information.

Finally, you release the lock using sp_releaseapplock and let all of the other callers have their chance to acquire the lock and discover that the data is fresh.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448