Table with status column that looks up history table based on date

• agibson83

  Newbie

  Points: 7

  More actions

  June 16, 2009 at 5:23 pm

  #138788

  I have a Users table with a CurrentStatus column which is a computed column that runs a UDF passing in the UserID and getdate(). The UDF queries a UserHistory table which contains ID, UserID, StartDate, EndDate, StatusID and returns StatusID for the user on the date passed in. I can then use this StatusID to lookup the StatusName on the Statuses table.

  This functionality is required for a web based system where user status can be changed in the past, present or future. The functionality works great however on the website list pages where i get all 2500 users out of the database and i need the StatusName column in the returned recordset, it has massive performance problems.

  I am trying to avoid doing server side paging if possible. Does anyone have experience with a similar sort of setup?

• Steve Jones - SSC Editor

  SSC Guru

  Points: 734434

  More actions

  June 18, 2009 at 12:34 pm

  #1011985

  You'd have to provide more information about the code here if you want help.

  There are performance problems with functions at times, so you want to minimize them in large data sets.