I would like to be guided, I have a stored procedure in a transaction that does the following:
The Sales table has a trigger that triggers when the "ProductId" column is changed, this to record the changes made in the "Sales" table in a blog
My problem: It generates a timeOut by default it was 30 sec, I have changed it to 2000 sec which is approximately 33.3 min. but time runs out and sends the timeout exception, can I optimize my procedure to not have that problem? I understand that once the transaction starts the tables are blocked, I need tips to complete the transaction.
UPDATE dbo.Cliente
SET ProductoID= @ProductoNuevoID,
WHERE ClienteID= @ClienteID;
INSERT INTO dbo.ClienteProductoBitacora
(
ClienteID,
ProductoAnterioID,
ProductoCambioID,
FechaCreacion,
UsuarioCreacion,
UsuarioDominioCreacion
)
VALUES
(
@ClienteID,
@ProductoAnterioID,
@ProductoCambioID,
GETDATE(), -- FechaCreacion - datetime
User_Name(), -- UsuarioCreacion - varchar(50)
@Usuario -- UsuarioDominioCreacion - varchar(50)
)
UPDATE dbo.Ventas
SET ProductoID= @ProductoCambioID
WHERE ClienteID = @ClienteID;
END
GO