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?