| Author |
Topic |
|
dbwilson4
Yak Posting Veteran
50 Posts |
 Posted - 2007-06-29 : 15:07:10
|
| At the moment, every record in any table has a status column. The Status is usually active, disabled, pending, closed, or deleted. ANY query filters out the deleted records.There are many relationships.Users can have documents, documents can have pages, pages can have instructors. Each document may have thousands of pages. Each page may have thousands of instructors.CURRENTLY:At the application layer, when a user is deleted...ALL relationship records are UPDATED to set the status column to delete. This update has to occur on MANY relationships. Once a week, a cleanup program runs that physically deletes all records marked for delete (status column).As you can tell, this application will probably not perform well when under high usage. So how should I handle this?Should i physically delete when a user is deleted? Should I run somekind of background task?? |
|
|
rudesyle
Posting Yak Master
110 Posts |
Posted - 2007-06-29 : 15:16:07
|
quote:
Originally posted by dbwilson4
At the moment, every record in any table has a status column. The Status is usually active, disabled, pending, closed, or deleted. ANY query filters out the deleted records.There are many relationships.Users can have documents, documents can have pages, pages can have instructors. Each document may have thousands of pages. Each page may have thousands of instructors.CURRENTLY:At the application layer, when a user is deleted...ALL relationship records are UPDATED to set the status column to delete. This update has to occur on MANY relationships. Once a week, a cleanup program runs that physically deletes all records marked for delete (status column).As you can tell, this application will probably not perform well when under high usage. So how should I handle this?Should i physically delete when a user is deleted? Should I run somekind of background task??
That's going to depend on yur requirements. DOes the client want to keep the records around? If delete really means delete, then I would delete right there on the spot.However, I have dealt with clients who want the ability to "undelete: at any time. THere, you may need to keep those records around. One thing you can do right off of the bat with Sql Server 2005 is partition a table. SO what you would end up having is one partition of active records, and another of inactive records. |
 |
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-06-29 : 15:43:49
|
| This is not a client app...this is an app I been working on for months (I have full control over the requirements heh).There is NEVER going to be the ability to "undelete" records. The reason the records are marked for deletes instead of physically deleting is for security reasons and the VERY RARE exception that someone might need to recover some data. Every week (probably on friday night at 12am) a special program will run that will physically delete all records marked for deletion.Partition by status is a very good idea but remember, the records must be marked for deletion first..this means an UPDATE query MUST run. Since there MAYBE thousands of relationships...would the update query run slow? Is there any solution to this? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-29 : 17:33:26
|
| I would call this a design error: A fact should be stored in the database only once.If the fact that a child is "deleted" depends on the status of the parent, have the queries lookup the status from the parent instead of trying to propagate the info to the children.CODO ERGO SUM |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-06-29 : 18:57:27
|
| So you are saying to check the PARENT items? For an example, check if the document record is deleted when viewing the page, instructor records?But wouldn't that create "orphans" or "ghosted" records or "redundant" records in the DB? It would accumulate over time without some physical delete queries be run weekly.I guess I'm stuck. I really don't know what to do. The application is designed so records can be marked as delete and if marked, it also marks any RELATIONSHIPS (not neccessarily children records). This would have low performance because it would have to update several relationships etc.I don't know if I should just directly physically delete it.What would you do? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-29 : 21:58:48
|
| I didn't say to not run the deletes. Just mark the parent for deletion, and delete it with your weekly process, daily, or whenever.To check if something is logically deleted, check the status of the parent. That way, you only have to update the parent to mark all the child rows as deleted. If the child can be deleted without the parent, set the status of the child row to deleted, and then check the status of both the parent and the child when you lookup an item.If you feel it is too much work to check both, it might be just as fast to delete immediately. Updates are often more resource intensive than deletes.CODO ERGO SUM |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-06-29 : 23:14:09
|
| Thanks! Now before I go rearchitect, if this was a myspace like site and TECHNICALLY...there wouldn't be a reason to not physically delete....would you physically delete?If going the physical delete route, would you recomend that I delete the all relationships when a parent is deleted OR mark it so that the weekly cleanup process will instead delete all the relationships? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-06-30 : 00:23:03
|
quote:
Originally posted by Michael Valentine JonesUpdates are often more resource intensive than deletes.
isn't an update the same as a delete followed by an insert? seems I read that somewhere once. if so delete is cheaper than update.
elsasoft.org |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-06-30 : 04:37:38
|
| "If delete really means delete, then I would delete right there on the spot.Me too. Copy the data to an Archive table if you need an audit history.Right now if I delete a record 1 second before the weekly cleanup I haven't got any time to get it back etc ..."isn't an update the same as a delete followed by an insert?"I assume not if the new record will "fit" in the space of the old record, but if the Delete Flag was NULL, and is now 1 then the record may have just got larger ..."If going the physical delete route, would you recommend that I delete the all relationships when a parent is deleted OR mark it so that the weekly cleanup process will instead delete all the relationships?"I would delete everything. I would use a trigger, so when I deleted a "parent" record all the Child records were deleted. That would allow me to delete a record from lots of different places without having to duplicate the logic for deleting all the related records - that logic would be centralised in a trigger for that table. (And the Trigger could force a ROLLBACK if it detected some invalid scenario - attempting to delete something that would break the referential integrity of the database).Each table could have its own trigger to delete more child records, and also to copy the records it deletes to an Audit table (if required)Kristen |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-06-30 : 15:00:43
|
| Ok, the trigger idea sound very good (I don't have to right extra code either in the application layer). I was always taught that triggers were bad and that it can cause a "domino" effect on CRUD actions but I guess in this case, triggers are the best solution.In the application layer, I wrap all CUD actions in TRANSACTIONS. Does this mean that if a delete occurs, any triggers that are triggered will run in the same transaction? This is important so if the trigger fails, the main transaction will fail causing a ROLLBACK. |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-06-30 : 15:02:01
|
| yes triggers are part of the transaction and their operations will be rolled back if a ROLLBACK occurs._______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-06-30 : 15:16:06
|
| BTW, How well would triggers work for deleting thousands of records in ONE GO? Or do you suggest deleting the parent only and then every week cleanout the relationships of the deleted parents?Also, in the trigger...I just want to delete any relationships....should I turn these options on (i dont want to turn on anything that is not needed) in the trigger options:Check Existed Data On creation: YesEnforce for replication: YesEnforce Foreign Key constraint: Yes |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-06-30 : 15:17:48
|
| To Jsmith8858:I'm not sure if I even created a trigger (I'm new to this trigger/constraints thingy in MS SQL 2005 management studio). I went into the table design mode, and opened up "Foreign Key Relationships". From there I created a relationship to the parent table from the child table and told it to cascade delete any child records when parent records are deleted. I'm not sure if that is called a "trigger". |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-06-30 : 16:34:54
|
quote:
Originally posted by dbwilson4
Ok, the trigger idea sound very good (I don't have to right extra code either in the application layer). I was always taught that triggers were bad and that it can cause a "domino" effect on CRUD actions but I guess in this case, triggers are the best solution.
Just in case you have got confused I believe Kristen was recommending the trigger for audit purposes (insert deleted records into a deleted records table to maintain a history). I don't think he meant to use them to enforce RI. To cover the RI requirement you can simply specify ON CASCADE.... options to your foreign key constraints and then deletes can be cascaded to children. There are some circumstances where you need triggers to enforce RI in SQL Server but these are reasonably rare.HTH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-01 : 16:24:45
|
| Yeah, the only reason to use a Trigger for a Delete-related-children-record is if there is some conditional Business Rules Logic involved, or SQL Server moans that the relationship is "circular", too complex, or somesuch and won't let you make a cascade delete FK.Kristen |
|
|
|
dbwilson4
Yak Posting Veteran
50 Posts |
Posted - 2007-07-01 : 17:24:51
|
| Sorry if I sound like a noob but I'm confusedIsn't cascade deletes the same thing as triggers? Like if I go into Studio Management 2005 and go into design mode of a table and click "relationship properties" and then create a relationship and tell it to cascade delete......is it a trigger? |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-07-01 : 17:41:39
|
quote:
Originally posted by dbwilson4
Isn't cascade deletes the same thing as triggers?
Nope  Check out CREATE TRIGGER in books online (if you come from a front end background it is a bit like an event procedure). Foreign keys are under constraints. |
|
|
|
|
Architectural: Best way to handle deletes