Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2007-12-10 : 09:06:59
|
| Hi,I really don't know how frowned upon my approach is here, but it was the only way I have been able I've been able to do it.On my application, when users delete their account, it sometimes brings the db server to a COMPLETE crawl. The reason is some users who delete have many years of related data, and when the data deletes with them, its very slow.To avoid this I've taken off many contstraints, and I do have some sprocs that deleted orphaned data at night.thoughts on this approach ? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 09:10:37
|
Only how you assign a new userid.If you reuse UserId, all old data becomes valid to new user with same id.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-12-10 : 09:13:05
|
Hi Peso,my userID is an Identity INT column, so its never re-used. this makes my plan ok ?  |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 09:22:24
|
I would flag the user as deleted instead of actually delete the user on demand.Then I would have clean-up tasks running each night to delete all users flagged as deleted.This also have some advantages. If user changes his/her mind, the user can easily "undelete" and have all data back.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2007-12-10 : 09:35:42
|
| agreed, I wish I had designed it this way years ago .. I will work towards getting it setup like you suggestion, I think it would be very beneficial too..thanks for your opinion, much appreciated as always :Dmike123 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-10 : 09:39:46
|
The deleted column may be a simple BIT, or it could be a datetime.If it is a BIT, have a job run each night deleting all users when this column is set to 1.If it is a DATETIME, have a job run each night deleting all users when this column is older than x number of days.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
|
|
|
|
deleting orphaned data - maintenance