CODEWITHSUNDEEP
sql-server
Amir
Amir

Reputation: 121

Delete from one table in my database(SQL server) is slow

I have one table that have two trigger for insert and update.The record number is 700000 and delete from table is slow,can this problem for triggers that is for insert and after update?

My Trigger : 

ALTER TRIGGER [dbo].[FillFinallAmount_UpdateTR] ON [dbo].[StuffPriceInfo] 
after Update AS 
--declare var
    select @fatherid=i.FatherPriceInfoId from inserted i; 
    select @priceid=i.PriceInfoId from inserted i; 
    select @stuffcodeseq=i.stuffcodeseq from inserted i; 
    select @StuffServiceCode=i.StuffServiceCode from inserted i; 
    select @PriceType=i.PriceType from inserted i; 
    select @TaskType=i.TaskType from inserted i; 
    select @basepercent=i.basepercent from inserted i; 
    select @NewAmount=i.NewAmount from inserted i; 
    select @FinalAmount=i.FinalAmount from inserted i; 
    select @dbasepercent=i.basepercent from deleted i; 
    select @dNewAmount=i.NewAmount from deleted i; 
    select @dFinalAmount=i.FinalAmount from deleted i; 
    select @dfatherid=i.FatherPriceInfoId from deleted i; 
    IF(@dbasepercent!=@basepercent or @dNewAmount!=@NewAmount or @dFinalAmount!=@FinalAmount or @dfatherid!=@fatherid) 
    BEGIN 
        IF update(FatherPriceInfoId) 
        BEGIN 
            IF @fatherid is not null 
            BEGIN 
                set @fatheramount=(select FinalAmount from StuffPriceInfo where PriceInfoId=@fatherid) 
                set @FinalAmount=((@fatheramount*@basepercent*0.01)+@NewAmount) 
                update StuffPriceInfo set FinalAmount=@FinalAmount where PriceInfoId=@priceid 
                update StuffPriceInfo set FinalAmount=((@FinalAmount*basepercent*0.01)+NewAmount)   where FatherPriceInfoId=@priceid 
            END 
            else 
            begin 
                update StuffPriceInfo set FinalAmount=@NewAmount where PriceInfoId=@priceid 
                update StuffPriceInfo set FinalAmount=((@NewAmount*basepercent*0.01)+NewAmount)   where FatherPriceInfoId=@priceid 
            END 
        END 
        ELSE if update(basepercent) 
        BEGIN 
            IF @fatherid is not null 
            BEGIN 
                SET @fatheramount=(select FinalAmount from StuffPriceInfo where PriceInfoId=@fatherid) 
                SET @FinalAmount=((@fatheramount*@basepercent*0.01)+@NewAmount) 
                UPDATE StuffPriceInfo set FinalAmount=@FinalAmount where PriceInfoId=@priceid 
                UPDATE StuffPriceInfo set FinalAmount=((@FinalAmount*basepercent*0.01)+NewAmount)   where FatherPriceInfoId=@priceid 
            END 
            ELSE 
            BEGIN 
                UPDATE StuffPriceInfo set FinalAmount=@NewAmount where PriceInfoId=@priceid 
                UPDATE StuffPriceInfo set FinalAmount=((@NewAmount*basepercent*0.01)+NewAmount)   where FatherPriceInfoId=@priceid 
            END 
        END 

AND .....

Upvotes: 1

Views: 76

Answers (1)

Amir
Amir

Reputation: 121

The problem resolve by create index for 'FatherPriceInfoId' column that has relation with 'PriceInfoId' column (both columns are in same table and 'PriceInfoId' is primary key)

Upvotes: 1

Related Questions