How to optimize the SQL command ...

[Paul Conrad]

What is your question?

"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon

[followait]

See the title.

[Paul Conrad]

Not sure where you can optimize it. Looks pretty tight. Why do you think it needs optimizing? Is it taking a long time to run?

"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon

[followait]

Yes. Two conditions duplicates.

[pmarfleet]

I don't think you can improve this. You can't escape duplicating the WHERE clause in the EXISTS and UPDATE queries. It's not really duplication anyway - the UPDATE query is only fired under certain conditions.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

[andyharman]

The following will remove the need for the "if exist" statement:

UPDATE t SET num=num+1
  WHERE name='abc'
  AND age=20
  AND job='worker'
IF (@@ROWCOUNT = 0) BEGIN
  INSERT INTO t(id,name,age,job,num)
     VALUES(NEWID(),'abc',20,'worker',1)
END

Additionally you should look at your indexes. I would expect something like:

CREATE UNIQUE INDEX t_idx1 ON t (name, age, job)

I would recommend looking at the other SQL that use this table, then change the order of the columns in the above index to best-suit their requirements. Regards Andy

[followait]

I'm using ADO. Any way? Thanks.

[andyharman]

It would slot-in in the same way as your original code sample. However a better way would be to wrap it in a stored procedure:

CREATE PROCEDURE dbo.Insert_t
@Name VARCHAR(40),
@Age SMALLINT,
@Job VARCHAR(40)
AS BEGIN
  SET NOCOUNT ON
  UPDATE t SET num = num+1
    WHERE name = @Name
    AND age = @Age
    AND job = @Job
  IF (@@ROWCOUNT = 0) BEGIN
    INSERT INTO t (id, name, age, job, num)
      VALUES (NEWID(), @Name, @Age, @Job, 1)
  END
  RETURN 0
END

You can then create an ADO command object, with parameters, to execute it from your code. Note that you will need to add some error-trapping code. I'll let you figure that all out. Regards Andy

[followait]

Thanks a lot. :)

[Ennis Ray Lynch Jr]

Interesting approach but I wonder if the failed update statement executes faster than the EXISTS statement? Either way, I don't like the approach beyond the novelty. In cases where I really need "extra" performance I separate the stored procedures and let the application decide which one to call, taking advantage of distributed logic.

Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway