How to optimize the SQL command ...

followait

IF EXIST(SELECT 1 FROM t where name='abc' ADN age=20 AND job='worker')
UPDATE t set num=num+1 WHERE name='abc' ADN age=20 AND job='worker'
ELSE INSERT INTO t(id,name,age,job,num) VALUES(NEWID(),'abc',20,'worker',1)

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