Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: Dynamic Update

  1. #1

    Default Dynamic Update

    I have a file which has 150000 update statements. what is the best way to execute it considering the performance and other factors in mind.

    I tried Execute Sql Script which is pretty slow. for 30000 records it took 75 mins.

    Sample as below

    update table1 set f1='n',f2='Harris',f3='Newyork' where id='ID1';
    update table1 set f2='n',f3='US',f4='Newyork' where id='ID2';
    update table1 set f1='n',f3='23456',f6='atlanta' where id='ID3';
    update table1 set f3='n',f6='citizen',f10='florida' where id='ID4';
    .
    .
    .
    Please advise.

  2. #2
    Join Date
    Jul 2009
    Posts
    476

    Default

    The more PDI-style way to do this would be to have an input file with the id value in one column and the update values in other columns, read that in a Text file input step, and then feed that to an Update step. It looks like your updates are a bit irregular - in one of them, you update f1, f2 and f3, in another f2, f3 and f4, etc., so the PDI-style way might not work for you.

    If you have to run this as a script, how much faster would it be if you put a "BEGIN TRANSACTION;" line at the beginning of the script file, and then add a "COMMIT TRANSACTION;" line at the end? Reducing the commit frequency might help.

  3. #3
    Join Date
    Aug 2016
    Posts
    290

    Default

    It would be better if you enable "transactional" connection. I don't have a new version so not sure how this is done on job level, but it can be done a transformation in the properties (Ctrl+T) under Miscellaneous tab. If you enable batch updates as well (you can add connection variables in the connection menu), it may be even faster?

  4. #4

    Default

    Thanks for the reply robj.

    If you have to run this as a script, how much faster would it be if you put a "BEGIN TRANSACTION;" line at the beginning of the script file, and then add a "COMMIT TRANSACTION;" line at the end? Reducing the commit frequency might help.

    Do I need to add this in Execute SQL Script?

  5. #5

    Default

    Thanks for the inputs Sparkles.
    I am using Pentaho 7.1. Could you please let me know where I can find this?
    If you enable batch updates as well (you can add connection variables in the connection menu)

  6. #6
    Join Date
    Aug 2016
    Posts
    290

    Default

    I think it depends on which database you use. If you go to "View" tab in spoon, expand Database connections and double-click on the one you use, then you get a "Database Connection" pop-up window. Navigate to "Options" where you can insert values like "Parameter" and "Value". For mysql, this would be "rewriteBatchedStatements" and "true". However this may only work if you have a step that includes a checkbox like "Use batch updates?", for example in the "Update" output step for transformations. You would also have to check the database sql log to confirm queries are re-written. This may only work if the transformation is not transactional.

  7. #7
    Join Date
    Jul 2009
    Posts
    476

    Default

    No, I would add the "BEGIN TRANSACTION;" line to the beginning of your script file, and the "COMMIT TRANSACTION;" line to the end of your script file, and then specify that script file in a SQL step in a Job.

    Were you trying to copy/paste all of the update statements into an Execute SQL Script step inside a Transformation? I would prefer the SQL step in a Job over that.

    Quote Originally Posted by harrisakbar.sm View Post
    Thanks for the reply robj.

    If you have to run this as a script, how much faster would it be if you put a "BEGIN TRANSACTION;" line at the beginning of the script file, and then add a "COMMIT TRANSACTION;" line at the end? Reducing the commit frequency might help.

    Do I need to add this in Execute SQL Script?

  8. #8
    Join Date
    Dec 2013
    Posts
    13

    Default

    indexing is not proper
    Thanks & Regards
    Karan Arora

  9. #9
    Join Date
    Aug 2016
    Posts
    290

    Default

    What indexing?

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.