SQL Server trigger performance

Question

I am using SQL Server 2005. I have this table of task files:

and a table of tasks:

When all the task of a file are all in a state 3 (completed), the state of the relative file has to be 4 (completed).

I try to resolve this problem adding a trigger on a task table, but I am not happy with performance, because the updates on the task table can be several per second, and i get database connection problem.

The trigger that I wrote is

CREATE TRIGGER [dbo].[tr_set_file_to_worked] 
ON [dbo].[PROD_TASK]
AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    ;WITH ToUpdate AS
    (
        SELECT
            pf.id_file, pt.id_state, COUNT(*) AS tasks
        FROM
            prod_task pt
        LEFT JOIN 
            prod_file pf ON (pt.ID_FILE = pf.ID_FILE)
        GROUP BY 
            pf.id_file, pt.id_state
    );

    UPDATE pf
    SET pf.ID_STATE = 4
    FROM prod_file pf
    INNER JOIN inserted ins ON (pf.Id_file = ins.id_file)
                            AND NOT EXISTS (SELECT *
                                            FROM ToUpdate tu 
                                            INNER JOIN inserted ins ON (tu.id_file = ins.ID_FILE)
                                            WHERE tu.id_state <> 3);
END

this query takes on average 200 ms.

Using the Squirrel solution takes 20 ms.

UPDATE pf
SET    ID_STATE = 4
FROM   inserted i           
       INNER JOIN PROD_FILE pf ON pt.ID_FILE = pf.ID
       LEFT JOIN PROD_TASK pt ON (i.ID_FILE = pt.ID_FILE AND pt.ID_STATE <> 3)
WHERE  i.ID_STATE = 3 AND pt.ID_TASK is NULL

Answer 1

hope i understand you correctly

UPDATE pf
SET    ID_STATE = 4
FROM   inserted i
       INNER JOIN PROD_TASK pt ON i.ID_FILE = pt.ID_FILE
       INNER JOIN PROD_FILE pf ON pt.ID_FILE = pf.ID
WHERE  i.ID_STATE = 3
AND    NOT EXISTS
       (
            SELECT *
            FROM   PROD_TASK x
            WHERE  x.ID_FILE = i.ID_FILE
            AND    x.ID_STATE <> 3
       )