SQL: Query timeout expired

I have a simple query for update table (30 columns and about 150 000 rows).

For example:

UPDATE tblSomeTable set F3 = @F3 where F1 = @F1

This query will affected about 2500 rows.

The tblSomeTable has a trigger:

ALTER TRIGGER [dbo].[trg_tblSomeTable]
   ON  [dbo].[tblSomeTable]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    declare @operationType nvarchar(1)
    declare @createDate datetime
    declare @UpdatedColumnsMask varbinary(500) = COLUMNS_UPDATED()


 -- detect operation type
if not exists(select top 1 * from inserted)
    begin
        -- delete
        SET @operationType = 'D'
        SELECT @createDate = dbo.uf_DateWithCompTimeZone(CompanyId) FROM deleted
    end 
else if not exists(select top 1 * from deleted)
    begin
        -- insert
        SET @operationType = 'I'
        SELECT @createDate = dbo..uf_DateWithCompTimeZone(CompanyId) FROM inserted
    end
else
    begin
        -- update
        SET @operationType = 'U'
        SELECT @createDate = dbo..uf_DateWithCompTimeZone(CompanyId) FROM inserted
    end


-- log data to tmp table

INSERT INTO tbl1
SELECT
    @createDate,
    @operationType,
    @status,
    @updatedColumnsMask,
    d.F1,
    i.F1,
    d.F2,
    i.F2,
    d.F3,
    i.F3,
开发者_JAVA百科    d.F4,
    i.F4,
    d.F5,
    i.F5,
    ...

FROM (Select 1 as temp) t
LEFT JOIN inserted i on 1=1
LEFT JOIN deleted d on 1=1  

END

And if I execute the update query I have a timeout.

How can I optimize a logic to avoid timeout?

Thank you.

This query:

SELECT  *
FROM    (
        SELECT  1 AS temp
        ) t
LEFT JOIN
        INSERTED i
ON      1 = 1
LEFT JOIN
        DELETED d
ON      1 = 1

will yield 2500 ^ 2 = 6250000 records from a cartesian product of INSERTED and DELETED (that is all possible combinations of all records in both tables), which will be inserted into tbl1.

Is that what you wanted to do?

Most probably, you want to join the tables on their PRIMARY KEY:

INSERT
INTO    tbl1
SELECT  @createDate,
        @operationType,
        @status,
        @updatedColumnsMask,
        d.F1,
        i.F1,
        d.F2,
        i.F2,
        d.F3,
        i.F3,
        d.F4,
        i.F4,
        d.F5,
        i.F5,
        ...
FROM    INSERTED i
FULL JOIN
        DELETED d
ON      i.id = d.id

This will treat update to the PK as deleting a record and inserting another, with a new PK.

Thanks Quassnoi, It's a good idea with "FULL JOIN". It is helped me.

Also I try to update table in portions (1000 items in one time) to make my code works faster because for some companyId I need to update more than 160 000 rows.

Instead of old code:

    
UPDATE tblSomeTable set someVal = @someVal where companyId = @companyId
    

I use below one:

    
declare @rc integer = 0
declare @parts integer = 0
declare @index integer = 0
declare @portionSize int = 1000

-- select Ids for update
declare @tempIds table (id int)
insert into @tempIds
select id from tblSomeTable where companyId = @companyId

-- calculate amount of iterations
set @rc=@@rowcount
set @parts = @rc / @portionSize + 1

-- update table in portions
WHILE (@parts > @index)
begin

    UPDATE TOP (@portionSize) t     
    SET someVal = @someVal      
    FROM tblSomeTable t     
    JOIN @tempIds t1 on t1.id = t.id        
    WHERE companyId = @companyId        

    delete top (@portionSize) from @tempIds     
    set @index += 1         

end

What do you think about this? Does it make sense? If yes, how to choose correct portion size?

Or simple update also good solution? I just want to avoid locks in the future.

Thanks