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?