Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Table with status column that looks up history table based on date Expand / Collapse
Posted Tuesday, June 16, 2009 5:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 24, 2009 6:19 PM
Points: 1, Visits: 2
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?
Post #736108
Posted Thursday, June 18, 2009 12:34 PM



Group: Administrators
Last Login: Yesterday @ 12:17 PM
Points: 34,363, Visits: 18,583
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.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #737828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse