November 6, 2008 at 4:31 am
Consider the following:
table PARENT:
fldID PK,
fldItemID FY to another table
fldValue
table CHILD_A:
fldParentID FK to parent,
fldChildValue
table CHILD_B:
fldParentID FK to parent,
fldChildValue
In the application the user is presented with a grid representing all of the existing and potential parent/child combinations. The user can modify or delete any value. When the user saves I want to save the state of the worksheet values in the parent and child tables. The application does not currently flag which combination (grid cell) has been modified. The entire grid is saved. Values of zero are not saved.
Here's the question for perfomance's sake which approach would be better:
(A) Delete all records belonging to a given item (fldItemID in PARENT) effectively clearing the slate. Child records would be dropped in cascade delete. Then create the new records for grid values greater than zero.
(B) For each possible grid value where the value is zero, delete that record combination. For all other grid values see if a record already exists. Update or insert as needed.
Assume there are 100 possible combinations.
(A) Would be efficient because a single command would wipe the slate clean. Under the covers there would be an index seek for the itemID in the parent table and an index seek for each child record. If this lookup were to happen for each record it would only happen for as many records as exist. There would only be as many inserts as the number of records where the value is greater than zero. The drawback is that the table would be reindexed after the delete and each insert. The table may contain > 1 million rows
(B) Could cause a delete on records which don't exist (the grid value was zero and remained zero). For each update a query would check if the record exists. If it doesn't exist the record is inserted. The benefit would be for records which were altered they would be altered in place and indexing may not suffer as much.
Please let me know if I'm making incorrect assumptions or if my coffee hasn't kicked in yet. I prefer A because I think the performace would be better with less individual queries and the update procedure would be cleaner which makes it less brittle and more maintainable.
Thanks for thoughts
November 7, 2008 at 6:42 am
No takers? Am I asking the wrong question?
Thanks
May 28, 2009 at 9:31 am
Partition data seems a better way to remove data in bulk.
However, what really is the performance cost for Delete.
e.g. for big and busy tables with quite a few indexes frequently been updated with holdlock, delete could be costly. But, how costly is it.
is it in general a good approach that you never delete the data but to update the flag with an index?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply