April 4, 2008 at 7:55 am
Since this was not clear this is in a GUI situation. Also assumes 1 screen where you have the current values and make changes and click an Update button
Here's the deal, I know we have all dealt with updates made to a record in stored procedures and I am wondering how you handle it. It seems like a simple topic, but there a lot of options and I wonder how the gurus here handle it.
For our example use this table:
CREATE TABLE [dbo].[Persons](
[Person_ID] [int] IDENTITY(1,1) NOT NULL,
[First_Name] [nvarchar](20) NOT NULL,
[Middle_Name] [nvarchar](20) NULL,
[Last_Name] [nvarchar](20) NOT NULL,
[Suffix_ID] [int] NULL,
[Birth_Date] [smalldatetime] NULL,
[Birth_City] [nvarchar](25) NULL,
[Birth_State_Province_Id] [int] NULL,
[Marital_Status_ID] [int] NOT NULL,
CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED
(
[Person_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
How would you write an update SP for this table?
Here are the ways I can think of doing it, feel free to add another:
Would you write multiple SP's for each field and make the UI call the correct SP?
One SP with a bunch of IF @parameter is null statements with dynamic SQL within the SP? How do you handle security in this one as the execution of dynamic sql is not in a different context and does not use ownership chaining?
One SP with IF @parameter is null with a different update for possible set of conditions?
One SP assuming all the parameters required so you update each column each time?
One SP defaulting parameters to null with one update using Coalesce(@parameter, column)?
Why do you do it the way you do and how has it scaled?
Any articles you would suggest on this topic? I did a search on this site and did not find an article on this in the first 6 pages of results on my search.
Thanks,
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2008 at 8:29 am
I don't have supporting documentation and lists of tests at hand, but we generally either call for all parameters to be passed and then update all the columns every time or use the COALESCE statement for NULL values in the parameters.
But each of those more or less assumes a row-by-row approach (which, I've found, updates for the most part to be). If I were doing it in batches, I would assume an XML document with the values needing updating and again, I'd expect to update all the columns every time.
I'll be interested to see what the truly hard-core people have to say about it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 4, 2008 at 8:37 am
That has been my typical approach as well Grant. I am looking for the hard core performance guru's approach as well.
This question assumes that the update is being done from a UI where the user is operating on a single record.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 4, 2008 at 8:56 am
The hidden assumption in the question seems to be "in a GUI situation". Meaning - how to write an update SP to be used in a GUI.
Given that - then yes, it's very common to be able to assume that you're only touching just one row at a time, in which case you really can't much help writing something that takes in parameters for each column. One of the big questions would be - how many screens lead up to the update? Should each screen/page update its own section of the row, or should that be done in one single update at the end?
Perf-wise, I'm not sure you can get really hardcore with a single row update. You can minimze how many updates happen per row, however, and THAT should help.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 4, 2008 at 9:08 am
First off consider what actually happens in an UPDATE. If you look at the transaction log (DBCC LOG or whatever the function is for 2005) you will find that in an update the operation is really a DELETE and an INSERT of the row (both before and after images are in the transaction log).
So what I pretty much always do is write a complete update SP with all parameters they would send from the UI and just for saftey (in case the UI doesn't validate a change) I have a where clause to ensure data actually did change otherwise not to update.
So in your case I might do something like this.
CREATE PROC Update_Persons_Full
@Person_ID int, -- Our Key so it doesn''t change.
@First_Name nvarchar(20),
@Middle_Name nvarchar(20),
@Last_Name nvarchar(20),
@Suffix_ID int,
@Birth_Date smalldatetime,
@Birth_City nvarchar(25),
@Birth_State_Province_Id int,
@Marital_Status_ID int
AS
SET NOCOUNT ON
UPDATE
dbo.Persons
SET
First_Name = @First_Name,
Middle_Name = NullIf(@Middle_Name,''), -- I like to get rid of invalid options I don't want stored just in case.
Last_Name = @Last_Name,
Suffix_ID = NullIf(@Suffix_ID,-1),
Birth_Date = @Birth_Date,
Birth_City = NullIf(@Birth_City,''),
Birth_State_Province_Id = NullIf(@Birth_State_Province_Id,-1),
Marital_Status_ID = NullIf(@Marital_Status_ID,-1)
WHERE
Person_ID = @Person_ID
AND ( -- This is where I validate something did change.
First_Name != @First_Name
OR IsNull(Middle_Name,'') != IsNull(@Middle_Name,'') -- Note I use this to resolve null comparisons as equal or not.
OR Last_Name != @Last_Name-- Try to use something that should not be valid.
OR IsNull(Suffix_ID,-1) != IsNull(@Suffix_ID,-1)
OR IsNull(Birth_Date,'19000101') != IsNull(@Birth_Date,'19000101')
OR IsNull(Birth_City,'') != IsNull(@Birth_City,'')
OR IsNull(Birth_State_Province_Id,-1) != IsNull(@Birth_State_Province_Id,-1)
OR IsNull(Marital_Status_ID,-1) != IsNull(@Marital_Status_ID,-1)
)
GO
But some of the stuff in the above are personal preferences I have developed over time.
April 4, 2008 at 9:10 am
Sorry, additionally do note I don't use default values for any fields as I want to be sure the application is providing all for this particular SP so things like not resubmitting the Middle Name doesn't cause the accidental loss based on my SPs inputs.
April 4, 2008 at 9:12 am
Matt Miller (4/4/2008)
Perf-wise, I'm not sure you can get really hardcore with a single row update. You can minimze how many updates happen per row, however, and THAT should help.
I agree here, performance is more the index on your key field(s) and not anything to do with the statement itself.
September 23, 2008 at 11:35 am
As anyone measured the cost of sending the whole list of parameters all the time on the network?
Also has anyone ever used a timestamp column to ensure that the row being updated is in fact still in the same state as it was when it was first accessed?
IE > We have a scheduling module in our application (currently running under access). Access does this little check in the where condition to see if the row is still the same ::
update dbo.table set... where col = @col and @id = @id and @col1 = col1...
It checks all the columns that are not updated to make sure they didn't change.
However you can do the exact same thing with a timestamp which to my calculations back then saved a lot of useless cpu cycles and network traffic.
The thing is that this schedule is currently used by a couple employees and they always end up changing the same fields on the same rows at different times on different pcs which causes some merge problems. How would you guys suggest me to deal with that?... check all the columns in the where, or use timestamp?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply