April 18, 2002 at 12:51 pm
I am a dba and have gotten into a difference of opinion with the 'Middle tier' developers and so want to know what others are doing:
I have been writing Sql code for a long time. For standard updates what I have been doing is: Written sprocs in a way where there is a 'select' sproc which sends all the fields in the row from a table to the 'GUI' or 'Middle tier' along with the timestamp for the row. The front-end makes changes to some of the fields and makes an update in a way that they call an update sproc which expects all the fields, regardless of whether they were updated or not. Makes the update if the timestamp sent back at the time of the update matches the selected timestamp.
Now some of the Middle tier developers want to pass in just the fields that have changed when calling the Update sproc. This would mean that inside the sproc I'll have to determine which fields they are sending in and then either dynamically build a Sql string for Update or else inside the sproc default the input parameter values to NULL, look for not null fields and do an 'Update'.
If you go the 'NULL' route another problem is that what if the caller of the 'Update sproc' actually wanted to send a value of 'NULL' as the updated value of the field. Then we get into creating our own kludgy protocols to get around this problem.
I want to enforce 'select all fields from a table and make whatever changes you want, but send all the fields back along with the timestamp' at the time of update. Reduce extra work for Sql Server, so it does not become a bottleneck when thousands of rows are being inserted and updated all the time.
What do you say? and is this not really a standard way everyone is doing it.
April 18, 2002 at 1:31 pm
There is a script in Misc.category "Preventing accidental overwrites with update SPs" that might give you some hints.
This is the part of it:
CREATE PROCEDURE UpdateAddress
@StreetNumber varchar(20) = NULL,
@StreetName varchar(100) = NULL
***
UPDATE Address
SET
StreetNumber = ISNULL(@StreetNumber, StreetNumber),
SteetName = ISNULL(@StreetName, StreetName)
****
I think it very neat decision
April 18, 2002 at 3:20 pm
The only problem with epol29 exaplme is the fact you stated that NULL may be what is being submitted. Here is the change that you need to make
CREATE PROCEDURE UpdateAddress
@StreetNumber varchar(20) = 'NoDataEntered',
@StreetName varchar(100) = 'NoDataEntered'
***
UPDATE Address
SET
StreetNumber = CASE WHEN @StreetNumber = 'NoDataEntered' THEN StreetNumber ELSE @StreetNumber END,
SteetName = CASE WHEN @StreetName = 'NoDataEntered' THEN StreetName ELSE @StreetName END
****
Now when they submit UpdateAddress @StreetNumber = 2340, @StreetName = NULL it will update the field with NULL. The key is to use something that the data will never be, I personally use NoDataEntered for varchars. Numerics can be done the same way using a varchar for the data then use CAST to change the variable when doing the update.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 18, 2002 at 3:47 pm
Well, NULL is more generic and it's exactly shows that either parameter was missing or there is nothing to update.
Also, any default messages could only be passed thru the string variables, what about numeric values? Yes, it can be set to 0 and then checked
Case when @MyVal = 0 then...
Again, it's a matter of preference.
April 18, 2002 at 4:16 pm
Not quite a standard. For instance, one of our apps uses optimistic locking, so there is no need for a timestamp check. One reason not to just update the entire record is if you want to audit changes at the column level, not log the entire row as being changed. What is their reason for wanting to send only the changes?
Andy
April 18, 2002 at 4:42 pm
Andy,
Say they don't send only the changed columns but all the columns, and as you said, you would want to log only the changed columns, which makes perfect sense; specially for a 300 column row where only one column may have been updated. Then the problem is to find out from within the update sproc which columns have changed, or were you thinkibg that they would maybe pass into the update sproc a bit for every input parameter and we would in the sproc look at these bits to determine which columns to update and audit/log.
Also, do you have a lot of update sprocs that just update a few of the columns in a row? do you have multiple update sprocs then which just deal with updating subsets of the row and not the whole row? Is there maybe no 'most commonly used way to update' ?
Certainly want to get your views on these.
April 18, 2002 at 7:56 pm
We pretty commonly have special purpose procs that might update only part of a row, or might update portions of several rows in different tables in a transaction.
The bitmap idea is sound, if a "bit" of work to implement. Nice part is they should know what has been changed in middle tier, saves you figuring it out all over again. Last year I had to do some custom replication work where I only sent changes (with a few business rules to make it interesting), I would log the entire row as changed, then at scheduled times I'd go through and compare each column with the current data to determine what had changed. I'd be concerned about the cost of doing that compare, especially with 300 cols, in a trigger, but the cost of doing it anywhere else is increased complexity.
Cant say that I would never just try to capture changes, but in general my preference is to save a complete copy of the row. At the same I put the triggers in place I set up a job to remove the history after x days so it doesnt grow forever.
Have to benchmark cpu and disk io, plus figure in disk space to see which is more efficient.
Andy
April 19, 2002 at 5:15 am
Hey epol29 I wasn't referring to not use it but he had a specific need to be met that defaulting to NULL would prevent
quote:
If you go the 'NULL' route another problem is that what if the caller of the 'Update sproc' actually wanted to send a value of 'NULL' as the updated value of the field.
I agree with Andy on most of his statements. And believe that you should just treat it a s a row update like my example will do (even though it updates to itself). If you need to audit these changes assume changes where made and right a before record to another table with a datetime field with GETDATE() as the default. The after should be in the production table so all you need is the before in the audit and the time the before was changed. Also in regards to the trigger removing rows I view this in a downside as this can slow your trigger down and thus the transaction itself slows. I would do that in a job that runs daily, most of mine are midnigth jobs and delete anything before 30-90 days back based on size issues and busniess requirements.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 19, 2002 at 5:21 am
Thats correct, I didnt state it clearly. Job removes old data, trigger just handles the changes.
Andy
April 19, 2002 at 12:42 pm
Hey thanks for sharing your ideas.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply