Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: Ask for tips to speed up database lookup, Insert, Upate

  1. #1
    Join Date
    Aug 2007
    Posts
    7

    Default Ask for tips to speed up database lookup, Insert, Upate

    Ask for tips to speed up database lookup, Insert, Upate.

    I can get database read 1000 rows/second. But for database lookup, insert, or update, the speed is 4 rows/second.

    I tried to run 5 copies of lookup simultaneously, but it appeared to be repeating doing the rowset 5 times. It was not distributing the work load.

    Another thing I want to try is to increase JVM memory cache size. Where is it set for running a Transformation/Job?

    Thanks,

    Michael

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

    Default

    Quote Originally Posted by handtalk View Post
    Ask for tips to speed up database lookup, Insert, Upate.

    I can get database read 1000 rows/second. But for database lookup, insert, or update, the speed is 4 rows/second.

    I tried to run 5 copies of lookup simultaneously, but it appeared to be repeating doing the rowset 5 times. It was not distributing the work load.
    Depends on what kind of hops you ... you can eithe copy or distrubute. For lookups it shouldn't matter (if the outgoing order is not important). For update/insert you may have some problems if you can't split up your input (e.g. all keys lower than x , ....)

    Another thing I want to try is to increase JVM memory cache size. Where is it set for running a Transformation/Job?
    A little tweak in the java command line of spoon.bat (or corresponding pan/kitchen script).

    Regards,
    Sven

  3. #3

    Default

    Check each step you are running independently -- the 'stream' will only go as fast as the slowest component/step.

    I used to use the Blocking Step to check each portion. Another option is to output the stream to file at the end of a step, then read that file and begin the next step. It sounds time-consuming, but from-the-large of determining where your bottleneck is, this is the best approach.

    If a step before the database insert/update is only running at 5 records/sec, the database step will only go 5 records/sec.

    edit: right-click on your transformation, change 'Nr of rows in rowset' to increase its size. This occasionally increases performance for more memory, but also helps if you are running and watching the log-analyzer window.
    Last edited by dhartford; 10-05-2007 at 01:34 PM.

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.