Post reply

Performance Help for transaction depenedency

  • April 28, 2020 at 10:12 am

    #3746933

    Hi,

    Can anyone help me in fine tuning this or any other suggestions also.I have a table which will have a set of transactions  and reference transactions if any. I am creating a job to purge transactions based on some conditions like date , full completed based on a status etc. If a single transaction cannot be purged need to make the flag of all the dependent transactions as N along with remarks of this transaction.  Tried with below logic which seems to be working. But my test server has around 914506. The derivation of dependent transactions is taking more than 7 hrs. Can any help on the best way to get implemented please ?

    Drop Table SampleTable
    go
    Create Table SampleTable
    (
    Tran_OU int,
    Tran_Type varchar(10),
    Tran_No varchar(20),
    Ref_Tran_OU int,
    Ref_Tran_Type varchar(10),
    Ref_Tran_No varchar(20),
    Update_Flag varchar(10),
    Remarks varchar(1000)
    )
    go
    Insert into SampleTable values (3,'PM_SA','SAD1',3,'PM_PI','C1','Y',NULL)
    Insert into SampleTable values (3,'PM_SA','SAD1',3,'PM_PI','C2','Y',NULL)
    Insert into SampleTable values (3,'PM_SA','SAD1',3,'PM_SDA','D1','Y',NULL)
    Insert into SampleTable values (3,'PM_SA','SAD1',3,'PM_SDA','D2','Y',NULL)
    Insert into SampleTable values (3,'PM_SA','SAD1',3,'PM_SDA','D3','Y',NULL)
    Insert into SampleTable values (3,'PM_PV','PV1',3,'PM_PI','C1','Y',NULL)
    Insert into SampleTable values (3,'PM_PV','PV1',3,'PM_PI','C3','Y',NULL)
    Insert into SampleTable values (3,'PM_PI','C1',3,'PUR_PO','PO1','Y',NULL)
    Insert into SampleTable values (3,'PM_PI','C1',3,'PUR_GR','GR1','Y',NULL)
    Insert into SampleTable values (3,'PUR_GR','GR1',3,'PUR_PO','PO1','N','Cannot Delete the same due to customer Request')
    Insert into SampleTable values (3,'PUR_PO','PO1',3,NULL,NULL,'Y',NULL)
    Insert into SampleTable values (3,'PUR_GRN','GRN1',3,'PUR_GR','GR1','N','Cannot Delete the same due to customer Request')
    Insert into SampleTable values (3,'PM_SA','SAD2',3,'PM_PI','C3','Y',NULL)
    Insert into SampleTable values (3,'PM_SA','SAD2',3,'PM_SAD','D4','Y',NULL)
    Insert into SampleTable values (3,'PM_SA','SAD3',3,'PM_PI','C4','Y',NULL)
    Insert into SampleTable values (3,'PM_SA','SAD3',3,'PM_SAD','D4','Y',NULL)
    Insert into SampleTable values (3,'PM_PI','C5',3,'PUR_PO','PO2','Y',NULL)
    Insert into SampleTable values (3,'PM_PI','C5',3,'PUR_GR','GR2','Y',NULL)
    --Insert into SampleTable values (3,'PUR_GR','GR2',3,'PUR_PO','PO2','Y',NULL)
    go
    ;with recursion1 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select Ref_Tran_OU, Ref_Tran_Type, Ref_Tran_No, 'N', Remarks
    from SampleTable(nolock)
    where Update_Flag = 'N'
    union all
    select Tran_OU, Tran_Type, Tran_No, 'N', Remarks
    from SampleTable(nolock)
    where Update_Flag = 'N'
    union all
    select a.Tran_OU, a.Tran_Type, a.Tran_No, 'N', b.Remarks
    from SampleTable a(nolock),
    recursion1 b
    where a.Update_Flag = 'Y'
    and a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,
    recursion2 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion1 b
    where a.Update_Flag = 'Y'
    and a.Tran_OU = b.Tran_OU
    and a.Tran_Type = b.Tran_Type
    and a.Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    union all
    select a.Tran_OU'Tran_OU', a.Tran_Type'Tran_Type', a.Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion2 b
    where a.Update_Flag = 'Y'
    and a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,recursion3 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select Tran_OU, Tran_Type, Tran_No, 'N', Remarks
    from recursion2
    where Update_Flag = 'N'
    union all
    select a.Tran_OU, a.Tran_Type, a.Tran_No, 'N', b.Remarks
    from SampleTable a(nolock),
    recursion3 b
    where a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,
    recursion4 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion3 b
    where a.Update_Flag = 'Y'
    and a.Tran_OU = b.Tran_OU
    and a.Tran_Type = b.Tran_Type
    and a.Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    union all
    select a.Tran_OU'Tran_OU', a.Tran_Type'Tran_Type', a.Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion4 b
    where a.Update_Flag = 'Y'
    and a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,final (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select *
    from recursion1
    union
    select *
    from recursion2
    union
    select *
    from recursion3
    union
    select *
    from recursion4
    )

    select distinct *
    into #final
    from final

    --select '#final 1',*
    --from #final

    update a
    Set a.Update_Flag = 'N',
    a.Remarks = b.Remarks
    from SampleTable a,
    #final b(nolock)
    where a.Tran_OU = b.Tran_OU
    and a.Tran_Type = b.Tran_Type
    and a.Tran_No = b.Tran_No
    and a.Update_Flag = 'Y'
    --and a.tran_type not in ('PM_PI','PM_SA','PM_PV')

    update a
    Set a.Update_Flag = 'N',
    a.Remarks = b.Remarks
    from SampleTable a,
    #final b(nolock)
    where a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and a.Update_Flag = 'Y'
    --and a.tran_type not in ('PM_PI','PM_SA','PM_PV')

    Drop Table #final

    ;with recursion1 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select Ref_Tran_OU, Ref_Tran_Type, Ref_Tran_No, 'N', Remarks
    from SampleTable(nolock)
    where Update_Flag = 'N'
    union all
    select Tran_OU, Tran_Type, Tran_No, 'N', Remarks
    from SampleTable(nolock)
    where Update_Flag = 'N'
    union all
    select a.Tran_OU, a.Tran_Type, a.Tran_No, 'N', b.Remarks
    from SampleTable a(nolock),
    recursion1 b
    where a.Update_Flag = 'Y'
    and a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,
    recursion2 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion1 b
    where a.Update_Flag = 'Y'
    and a.Tran_OU = b.Tran_OU
    and a.Tran_Type = b.Tran_Type
    and a.Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    union all
    select a.Tran_OU'Tran_OU', a.Tran_Type'Tran_Type', a.Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion2 b
    where a.Update_Flag = 'Y'
    and a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,recursion3 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select Tran_OU, Tran_Type, Tran_No, 'N', Remarks
    from recursion2
    where Update_Flag = 'N'
    union all
    select a.Tran_OU, a.Tran_Type, a.Tran_No, 'N', b.Remarks
    from SampleTable a(nolock),
    recursion3 b
    where a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,
    recursion4 (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select a.Ref_Tran_OU'Tran_OU', a.Ref_Tran_Type'Tran_Type', a.Ref_Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion3 b
    where a.Update_Flag = 'Y'
    and a.Tran_OU = b.Tran_OU
    and a.Tran_Type = b.Tran_Type
    and a.Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    union all
    select a.Tran_OU'Tran_OU', a.Tran_Type'Tran_Type', a.Tran_No'Tran_No', 'N', b.Remarks
    from SampleTable a(nolock),
    recursion4 b
    where a.Update_Flag = 'Y'
    and a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and b.Update_Flag = 'N'
    )
    ,final (Tran_OU, Tran_Type, Tran_No, Update_Flag, Remarks)
    as
    (
    select *
    from recursion1
    union
    select *
    from recursion2
    union
    select *
    from recursion3
    union
    select *
    from recursion4
    )

    select distinct *
    into #finale
    from final

    --select '#final 2',*
    --from #finale

    update a
    Set a.Update_Flag = 'N',
    a.Remarks = b.Remarks
    from SampleTable a,
    #finale b(nolock)
    where a.Tran_OU = b.Tran_OU
    and a.Tran_Type = b.Tran_Type
    and a.Tran_No = b.Tran_No
    and a.Update_Flag = 'Y'
    --and a.tran_type not in ('PM_PI','PM_SA','PM_PV')

    update a
    Set a.Update_Flag = 'N',
    a.Remarks = b.Remarks
    from SampleTable a,
    #finale b(nolock)
    where a.Ref_Tran_OU = b.Tran_OU
    and a.Ref_Tran_Type = b.Tran_Type
    and a.Ref_Tran_No = b.Tran_No
    and a.Update_Flag = 'Y'
    --and a.tran_type not in ('PM_PI','PM_SA','PM_PV')

    Drop Table #finale

    select *
    from SampleTable?

     

     

  • April 29, 2020 at 11:10 am

    #3747261

    Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply