Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Upsert Performance - Insert / Update v.s. Table Output with Error Handling

  1. #1
    Join Date
    Nov 2008
    Posts
    16

    Default Upsert Performance - Insert / Update v.s. Table Output with Error Handling

    I am new to Kettle, currently using version 3.1.0 with postgresql 8.3.3. When doing an "Insert / Update", I can only get about 60~80 r/s. However, if I switch to "Table Output" with an "Update" error handling step, I can get about 3000 r/s for insertions, and 60~80 r/s for updates.

    I wonder if such behavior is normal, or there is something wrong with my setup. The lookup column has a unique index, and the table itself is pretty fat with 24 columns (mostly integers).

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Seems about right, at least in line of what I see... insert/update will first query the database for the row, check the columns to compare if the row exists and then either insert or update. So there's a lot more going on in the background.

    Regards,
    Sven

  3. #3
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    60-80 r/s sounds way too slow actually.

    Mmm, for PostgreSQL, the new bulk loader is really really fast (**). I've been getting upwards of 40,000 rows/s insert capacity (15MByte/s completely I/O bound) ... on my laptop.
    So it might be interesting to see if you can do a bulk load in a temporary table and then do a merge or something like that. That might even be faster.
    However, as always, the complexity increases the faster you want to go.

    All the best,
    Matt



    Disclaimer: It's experimental so that means: if it works for your particular security setup, remembering that all sorts of things could go wrong, etc, etc.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    One last thought : make absolutely sure you created the right index to do the lookups in the first place.

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.