Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: table Locking using Sync after merge

  1. #1
    Join Date
    Aug 2015
    Posts
    313

    Default table Locking using Sync after merge

    Hi,

    I'm using PDI-CE-6.0, java 1.7 64 bit mixed mode, MySQL database innodb. if my question is relevent in PDI forum if related to MySQL forum, will delete this thread but i have a hope on your suggestions.

    I am observing slowness issue while load data using Synchronize after merge with merge rows(diff). as per my POC when i kept NOLOCK on my target table then it is some what better. Could you please suggest if someone having the same issue with this step.

    in below step my target table is prod_regn_1 and one of the extracting table also same for merge rows(diff) step. table lock will be occur while writing on target thats why i kept NOLOCK.

    Name:  NOLOCK.jpg
Views: 265
Size:  12.5 KB

    thank you

  2. #2
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    No expert, but if you concurrently read and write the same table there's always a problem of records being locked.
    I would create a temp table to store results from Merge Rows and after that operation has finished I would insert/update into prod_reign 1
    -- Mick --

  3. #3
    Join Date
    Aug 2011
    Posts
    360

    Default

    Same answer, i usually have sorting step before the merge, so all lines
    are read before entering merge, such that there is no lock on output.
    OR, you can put a blocking step after the merge step, don't forget to check
    "Output all rows" or you'll get only one row!
    This could be faster than inserting into another table in order to get the rows back
    in another transformation!

    Last solution: do not use the merge step, if you have the reference and compare table in same database,
    and execute 3 SQL scripts in a jobs to handle inserts, updates and deletes by doing inner ans outer joins.
    A "bulk" synchronization

  4. #4
    Join Date
    Aug 2015
    Posts
    313

    Default

    Quote Originally Posted by Mathias.CH View Post
    Same answer, i usually have sorting step before the merge, so all lines
    are read before entering merge, such that there is no lock on output.
    I hope you are talking about Sort Rows step, am i correct ?. basically Merge rows(diff) with sync after merge step will work correctly when we use order by clause in extraction query. so i already kept sorting technique in my .ktr.

    as per your suggestion i will remove order by clause in my extraction query and will place sort rows after merge rows(diff). you ask me to kept sort rows step after merge rows(diff) step OR before step?

    Quote Originally Posted by Mathias.CH View Post
    OR, you can put a blocking step after the merge step, don't forget to check
    "Output all rows" or you'll get only one row!
    This could be faster than inserting into another table in order to get the rows back
    in another transformation!
    sure, i am aware of this step and i was tried long back. observed there is small performance difference with and without Blocking step. but will try again and update the status asap.

    Quote Originally Posted by Mathias.CH View Post
    Last solution: do not use the merge step, if you have the reference and compare table in same database,
    and execute 3 SQL scripts in a jobs to handle inserts, updates and deletes by doing inner ans outer joins.
    A "bulk" synchronization
    you are suggesting about synchronize after merge step(not to use) . I was tried with Insert/update ,Update steps. rather than these two Sync after merge step is very fast that's why i used this.

    as per your comments, which methodology or which steps are best to insert new records and update for existing records.

    Thank you
    Last edited by santhi; 11-18-2015 at 04:54 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.