Is UPDATE worth the effort (in this case)?

Users will modify (add, delete, update) their configuration settings with th
e
subset of all available settings. The more I think about about the effort
which will have to go into figuring out whether the row is an update or
insert, and then (if update) wheter the column value is valid the more
I'm leaning towards query/delete/insert methodology. Some of the INSERTs,
of course, could have been an UPDATEs but this way I'd be free from having
the obligation to figure it out.
There is no way that I could throw set of results at the configuration table
and say "you (DB in this case) figure it out which is UPDATE and which is
INSERT", is there? Is deleting and then inserting new rows, without regard
for the fact that for example only one row out of ten or twenty would have
been updated if I went thru the trouble of identifying it, a reasonable
solution?If you have a fairly busy system, the overhead of deleting and inserting
instead of just updating could cause some locking contention and other
activity that could hurt performance. INSERT's and DELETE's are not only
changing data on pages but perhaps allocating and deallocating pages,
causing page splits, affecting indexes, etc. -- a lot more for the server
to do.
I'd say you are better off doing an
IF EXISTS (...)
UPDATE ...
ELSE
INSERT ...
Just make sure the columns on the WHERE clause are properly indexed and your
performance will be good. When it boils down to it, the extra effort is
very little and in the long run I think you will be better off
Mike
"Art" <Art@.discussions.microsoft.com> wrote in message
news:6ACD7684-FC66-4AFA-8FC0-736E620A2B6F@.microsoft.com...
> Users will modify (add, delete, update) their configuration settings with
> the
> subset of all available settings. The more I think about about the effort
> which will have to go into figuring out whether the row is an update or
> insert, and then (if update) wheter the column value is valid the more
> I'm leaning towards query/delete/insert methodology. Some of the
> INSERTs,
> of course, could have been an UPDATEs but this way I'd be free from having
> the obligation to figure it out.
> There is no way that I could throw set of results at the configuration
> table
> and say "you (DB in this case) figure it out which is UPDATE and which is
> INSERT", is there? Is deleting and then inserting new rows, without regard
> for the fact that for example only one row out of ten or twenty would have
> been updated if I went thru the trouble of identifying it, a reasonable
> solution?|||On Wed, 19 Apr 2006 08:23:02 -0700, Art wrote:

>Users will modify (add, delete, update) their configuration settings with t
he
>subset of all available settings. The more I think about about the effort
>which will have to go into figuring out whether the row is an update or
>insert, and then (if update) wheter the column value is valid the more
>I'm leaning towards query/delete/insert methodology. Some of the INSERTs,
>of course, could have been an UPDATEs but this way I'd be free from having
>the obligation to figure it out.
>There is no way that I could throw set of results at the configuration tabl
e
>and say "you (DB in this case) figure it out which is UPDATE and which is
>INSERT", is there? Is deleting and then inserting new rows, without regard
>for the fact that for example only one row out of ten or twenty would have
>been updated if I went thru the trouble of identifying it, a reasonable
>solution?
Hi Art,
In addition to Mike's reply, the DELETE/INSERT approach will also cause
problems if the table is referenced by any foreign keys.
Mike posted a code skeleton for single-row operation; for multi-row
operation, use this instead:
UPDATE t
SET Column1 = new.Column1,
Column2 = new.Column2
FROM YourTable AS t
INENR JOIN OtherTable AS new
ON new.KeyColumn1 = t.KeyColumn1
AND new.KeyColumn2 = t.KeyColumn2
--
INSERT INTO YourTable (KeyColumn1, KeyColumn2, Column1, Column2)
SELECT new.KeyColumn1, new.KeyColumn2, new.Column1, new.Column2
FROM OtherTable AS new
WHERE NOT EXISTS
(SELECT *
FROM YourTable AS t
WHERE new.KeyColumn1 = t.KeyColumn1
AND new.KeyColumn2 = t.KeyColumn2)
Add error-handling as required, of course! :-)
Hugo Kornelis, SQL Server MVP|||Well Celko and ilk may leap at my throat, but I have previously solved
this problem as follows, it may be potentially shaky, I guess, but it
reduces overhead to a minimum (I think):
UPDATE MyTable ... WHERE CriteriaOfRowsWhichMayNotExist
IF @.@.ROWCOUNT=0
INSERT MyTable...|||On 25 Apr 2006 07:17:47 -0700, ben.simkins@.gmail.com wrote:

>Well Celko and ilk may leap at my throat, but I have previously solved
>this problem as follows, it may be potentially shaky, I guess, but it
>reduces overhead to a minimum (I think):
>UPDATE MyTable ... WHERE CriteriaOfRowsWhichMayNotExist
>IF @.@.ROWCOUNT=0
> INSERT MyTable...
Hi Ben,
Good, but not perfect.
If the number of rows to insert or update will never be more than one,
you'll have no pproblems (but in that case, you have llimited the
scalability of yoour solution).
In code that is designed to handle multi-row operations, this might
break in the following scenario:
- A total of 5 rows should be updated OR inserted.
- Only 3 of them already exist.
- The UPDATE will affect these 3 rows.
- @.@.ROWCOUNT = 3, not 0. Therefore the INSERT is skipped.
A safer way that handles both single-row and multi-row operations is
UPDATE MyTable ... WHERE CriteriaOfRowsWhichMayNotExist
INSERT MyTable ... WHERE NOT CriteriaOfRowsWhichMayNotExist
Hugo Kornelis, SQL Server MVP