Pefromance help for transaction derrivation

  • 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

     

  • I see you have quite a large chunk of code there and from a quick eyeballing of it, is it duplicated code?  Do you really need to go through those 4 layers of recursion twice  to get the output you expect?  If so, could you post some sample data that results in needing that second level of recursive CTE's followed by 2 updates?

    Looking at your sample data, those last 2 updates update 0 rows.  So removing 1/2 of the CTE's and 1/2 of the UPDATES  may help performance a bit.

    Again, just looking at your sample data, but as CTE final is using UNION, the DISTINCT on your select from final is not needed and will likely cause a performance hit.

    Based on your sample data, you can also change final to only select from recursion3 unioned to 4.  1 and 2 exist in 3 and 4 is only missing 1 row from 3.  Reducing your unions from 4 to 2 (as those are all pulling distinct lists from semi-large sets of data) should improve performance.

    The above being said, I only tested it with the provided sample data.  My proposed changes should be tested to see if it works with your real data.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • Hi,

    The above is duplicated code.

    I have identified that the below code is taking the time for the same as multiple recursion is going on as 1 transaction has multiple dependency.It appears to be an infinite loop. Can any 1 suggest an ideal solution for this if possible please.

    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'
  • The full, unlimited Cartesian join will be huge overhead.  At least turn the match of the tables into a standard join:

    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)
    inner join recursion1 b
    on 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'

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Changing your "UNION ALL"s to "UNION"s so you remove duplicates in the recursive lookup may help.

    I also agree with Scott here that you should update your joins to be in the standard format.

    I think that you likely do not need 4 recursive CTE's, let alone 8.  I expect this can be done with 1.  I just don't understand the logic of how you are deciding what makes your update flag get set to N.  I thought it was by following the path from Tran to Ref all the way up until you hit an "N" at which point all of them in that chain will be set to N ,but there are cases where that isn't true.

    I think your results are wrong OR I don't understand the logic.  What I think is wrong is you have some things marked as "N" in your final result that I do not think have a "path" from child (Tran) to parent (Ref) OR I don't understand the logic.  The items I am referring to are found by this query:

     WITH cte AS (
    SELECT *, CAST(tran_OU AS VARCHAR(50)) + TRAN_TYPE + TRAN_NO AS CHILDID, CAST([Ref_Tran_OU] AS VARCHAR(50))+[Ref_Tran_Type] + [Ref_Tran_No] AS PARENTID
    FROM SampleTable)
    SELECT * FROM cte
    WHERE parentID NOT IN (SELECT childID FROM cte)

    What I was trying to do was create a list of REF items that have no TRAN item and therefore there is no parent-child path to follow.  I got 11 rows which seemed high since there are 19 items total in the sample data.  Since there is only a relationship on 8 of the items, AND those 8 items all fall back to one of the 2 that say not to update, I think your original query is wrong OR I misunderstand some things.

    One thing I noticed is that a single Tran Type and Tran No pair can have multiple Ref_Tran_Type and Ref_Tran_No's associated with it.  My understanding is that if ANY of the Tran Type and Tran No's have an update flag of 'Y', then all of them should.  This is easy and trivial to do without recursive CTE's once you have found a path from Tran up to an update flag of N (if a path exists)

    But in your sample data, I do not see how certain rows relate back to a "N" row.  For example, rows where the Tran_Type is PM_SA and the Tran_No is SAD2 OR SAD3.   Using the SAD2 as an example, the Ref Tran Type is either PM_PI with Ref Tran No of C3 OR is PM_SAD and D4.  PM_PI with C3 does not exist as a Tran Type and Tran No and neither does PM_SAD and D4, yet your recursive CTE's are putting those as do not update.  I cannot see any reason why these should be N's.

    I think I need a better explanation of HOW the Tran side and Ref side are supposed to relate to each other AND if it is expected to have no real "root" (ie some paths do not have a row where the Ref is NULL) in some cases.  If that is expected, I think using my above query to create "fake" roots would make doing this with 1 recursive CTE instead of 8 possible and likely a LOT faster than 8.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Viewing 5 posts - 1 through 4 (of 4 total)

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