Hallo NG,

Consider the SQL queries attatched below. I have two versions each for
two different tasks I want to perform. The one task performs either an
insert or update, depending on a condition. The other task performs a
delete.

I've used the "Display Estimated Execution Plan" option in MS SQL
Server Management Studio for SQL Server 2005 to compare the different
versions of my stored procedures. I ran this for the following batches,
to determine the relative cost of each SP.

Batch 1:
EXECUTE update_ver1 1, 42 -- cost 36%
EXECUTE update_ver2 2, 42 -- cost 64%

Batch 2:
EXECUTE delete_ver1 1 -- cost 80%
EXECUTE delete_ver2 2 -- cost 20%

Now, I don't consider myself to have an expert understanding of SQL
Server's innerworkings. Thus, can anyone explain the results? I
expected the difference to be much less.

Regarding update_ver1 and update_ver2:
The nature of my real queries, on which these are based, is that mostly
updates are performed. Based on this, I thought it might be better to
always perform the update (update_ver2), since the insert would be the
exception. Another reason I expected update_ver2 to be better is that I
save the explicit select in the if's condition of update_ver1 -- this
makes sense if one exploits the fact that the update has to
(implicitly) do the work anyway.

Regarding delete_ver1 and delete_ver2:
I partially followed the same reasoning as above for these SPs. Won't
the delete statement of delete_ver1 do some sort of lookup anyway (as
is explicitly done in delete_ver1)?

Looking forward to your comments.

Regards
Pieter Rautenbach

-----------------------------------------------------------------------

CREATE TABLE [dbo].[tab1] (col1 INT PRIMARY KEY, col2 INT)

-----------------------------------------------------------------------

CREATE PROCEDURE [dbo].[update_ver1]
  @param1 INT,
  @param2 INT
AS
BEGIN
  SET NOCOUNT OFF
  IF EXISTS (SELECT col1 FROM tab1 WITH (NOLOCK) WHERE col1 = @param1)
    UPDATE tab1 SET col2 = @param2 WHERE col1 = @param1
  ELSE
    INSERT INTO tab1 (col1, col2) VALUES (@param1, @param2)
  END
END

-----------------------------------------------------------------------

CREATE PROCEDURE [dbo].[update_ver2]
  @param1 INT,
  @param2 INT
AS
BEGIN
  SET NOCOUNT OFF
  UPDATE tab1 SET col2 = @param2 WHERE col1 = @param1
  IF @@ROWCOUNT = 0
    INSERT INTO tab1 (col1, col2) VALUES (@param1, @param2)
  END
END

-----------------------------------------------------------------------

CREATE PROCEDURE [dbo].[delete_ver1]
  @param1 INT
AS
BEGIN
  SET NOCOUNT OFF
  DELETE FROM tab1 WHERE col1 = @param1
END

-----------------------------------------------------------------------

CREATE PROCEDURE [dbo].[delete_ver2]
  @param1 INT
AS
BEGIN
  SET NOCOUNT OFF
  IF EXISTS (SELECT col1 FROM tab1 WITH (NOLOCK) WHERE col1 = @param1)
    DELETE FROM tab1 WHERE col1 = @param1
END

-----------------------------------------------------------------------

<DIV>&quot;Pieter Rautenbach&quot; &lt;parautenb***@gmail.com&gt; wrote in
I would use these.  There's no need to do the leading lookup.  It's hard to
correctly lock, and, as you pointed out, the UPDATE is the more common case,
and it's optimized by just trying it and performing the insert.

For the DELETE there's absolutely no need to perform the SELECT.

David

Show quoteHide quote

Get last date in result set
TOP 10 for each group
UDF that take a UDT as a parameter (CLR)
Table reaching max value of identity very early
Crosstab, Pivot?