1

I need to implement user risk scoring in model

  1. The user risk scoring must compile dynamically on changes (eg: profile, transaction) - meaning for any changes on profile or any incoming transaction, the new risk scoring value needs to be be able to reflect immediately

  2. It will span across 10+ different tables

  3. The query with user list need to be able to filter by RiskScore

Options

  1. I can create a user defined function [udf].[CalculateRiskScore] and make a computed column in [dbo].[User] or query out during view The advantage with this is good maintainability but bad performance.

  2. Another way is to have a trigger for those 10 tables to call [udf].[CalculateRiskScore] and update [RiskScore] into [dbo].[tblUser] with indexing on [RiskScore]. The advantage with this is good performance but poor maintainability since now i have to repeat x times depending on how many table that will affect [RiskScore].


There may be another solution I am not aware of. Is there any way that I can get the best of both worlds while adhering to the requirement?

My objective is to call [udf].[CalculateRiskScore] in one single place only while not sacrificing performance of filtering user with [RiskScore]

  • 1
    Is there a reason that the logic in the function has to be encapsulated in a UDF? – Erik Darling Sep 6 '19 at 12:06
  • 1
    Another option is to create a materialized view (indexed views in SQL Server) based on a view that returns the risk score paired with the user primary key. – Brian Sep 6 '19 at 12:59
  • @ErikDarling to make it easier to maintain. I couldn't think of other way of easier maintanence other than encapsulating in udf. – c448548 Sep 6 '19 at 13:03
  • 1
    If it's a computed column definition, it's already encapsulated. Changing the column definition, or changing the UDF definition if it's referenced in a computed column is the same thing. Also, look at this Q&A: Is there a way to prevent Scalar UDFs in computed columns from inhibiting parallelism?. – Erik Darling Sep 6 '19 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.