Post reply

Optimal way to record a row as "needing something done" to it

  • January 29, 2021 at 11:47 am

    #3838689

    I'm hoping this is going to be a quick question which some of you clever people out there will be able to quickly answer.

    I'm working on a product in which a lot of tables have a "needs recalculation" type of column. These are usually set by a trigger on the table, which detects whether values in certain fields have changed. (Or it might also be set by some stored procedure.) A later process will

    1. Find the records needing recalculation by looking at the "needs recalculation" column.
    2. Do the recalculation.
    3. Reset the "needs recalculation" column.

    I don't really think this is a terribly good approach and am thinking about alternatives. Unfortunately, any improvement has to be made entirely within the database, so I'm considering removing the "needs recalculation" columns and instead having a message-based system in which the existing triggers will send a message and the later process will

    1. Read all the messages.
    2. Do the recalculation.
    3. That's it!

    As for what messaging system to use, the obvious candidates are:

    1. SSB. Seems a little overkill as there's a lot of set up, framework, etc. and we wouldn't be making use of the asynchronous processing aspect - we don't want or need the recalculation to be done every time a data change is made.
    2. A simpler, table-based approach in which the trigger simply writes records (consisting of a single integer) to a new table and the later process deletes all the records with a "OUTPUT deleted.ID" clause to get details of the records needing recalculation.

    Does anybody have any experience of meeting this sort of requirement? Thanks.

  • January 29, 2021 at 3:43 pm

    #3838789

    I don't think the current method is a bad approach.

    You can use a filtered index to identify the rows needing recalculated.

    IF the calcs were easy, I guess you could put them directly in the trigger, but generally you want a trigger to exec as quickly and simply as possible and get out, leaving any time consuming processing until later.

     

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply