Update column of a large set of records with a calculated value

Question

I am trying to figure out what a good way would be to write the pseudo query below in TSQL taking into account that there will be about 50 actualResult records and 100.000 prediction records per actual result involved.

update prediction
set points = (
    @points = 0
    if(prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB)
        @points = @points + 3
    if (prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB)
        @points = @points + 1
    select @points
)   
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

what I am wondering about is:

• Is there a way of doing the calculation which in reality is more complex inline? Right now I can only get it to work if I put it in a function but from what I understand this means it's constantly re-calculted even if the input is the same (which I quite likely in allot of the predictions)
• Would this way lock the entire range of records? And if so is there a way to prevent this? For example by copying it all to a table variable and then doing the calculation on that and then updating the records once all calculations are done?

am I on the right track here or would you write it in a different way?

Answer 1

Is there a way of doing the calculation which in reality is more complex inline?

If you can fold the logic in to an inline statement, perhaps using CASE as shown below, it will typically outperform a scalar UDF (user defined function) by orders of magnitude. The argument for a UDF is usually encapsulation of a complex or common calculation but if its only required in this one query, might as well inline it.

UPDATE
    p
SET
    p.Points = p.Points +
            CASE 
                WHEN p.valueA = a.valueA AND p.valueB = a.valueB THEN 3
                WHEN p.valueA = a.valueA OR p.valueB = a.valueB THEN 1
                ELSE 0
            END
FROM
    dbo.Prediction p
INNER JOIN
    dbo.ActualResult a
ON  a.Id = p.ActualId
WHERE
    a.Id IN (subquery)

Would this way lock the entire range of records?

You need to understand the concept of isolation levels to determine a) what will be locked and perhaps more importantly b) what should be locked. In short, at the default READ COMMITTED isolation level a shared lock will be taken and immediately released on each row in each table as it is read to perform the join. Rows in Prediction that will be updated will be protected with update and then exclusive locks on the rows which will be held until the statement (or parent transaction) is committed.

Depending on the distribution and volume of updates, you may find rows in the above substituted for pages or worst case tables.

And if so is there a way to prevent this? For example by copying it all to a table variable and then doing the calculation on that and then updating the records once all calculations are done?

You can't prevent the locking of rows that are being updated or inserted, else ACID compliance would be compromised. You can reduce the locking on rows that are being read but you may need to consider the opposite and increase the isolation level. If other processes insert or update records while this update occurs, you could end up with a miscalculation.

As per Isolation Levels in the Database Engine...

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.

A Profiler trace on Lock:Acquired/Released events and/or use of trace flag 1200 on an isolated test machine can be very useful for understanding the sequence of locks applied and released at different isolation levels.

Answer 2

Try this:

update prediction
set points = 
    (
        0 
        + 
        CASE WHEN prediction.valueA = actualResult.valueA AND prediction.valueB = actualResult.valueB
        THEN 3 ELSE 0 END
        +
        CASE WHEN prediction.valueA = actualResult.valueA OR prediction.valueB = actualResult.valueB
        THEN 1 ELSE 0 END
    )   
join actualResult on prediction.actualid = actualResult.id
where actualResult.id in (some subquery)

AFAIK - if function is not depend on data in query, then it calls one time per query,

AND

if the function is deterministic and schema bound - it will not call multiple times for the same data, BUT there has to be an index on argument data AND optimizer must want to use that index

If you want per-row locking, use ROWLOCK hint

Answer 3

One other thing you could possibly do if you're worried about locking large portions of a table or involving an underlying trigger in thousands of calls at the same time is to look at more of a looping / chunk approach. Given that you have ~50 ActualResult records, you could loop through those 1-5 at a time and most likely get decent performance. That might also save you from having your row locks escalate into table locks.

Answer 4

If goal is to keep prediction up-to-date according to data in actualResult table, you may try using a different approach. Write AFTER INSERT,UPDATE,DELETE trigger that UPDATE prediction set valueA = valueA +3(or -,depends on dml) WHERE ....