Uploaded image for project: 'Pentaho Data Integration - Kettle'
  1. Pentaho Data Integration - Kettle
  2. PDI-11363

Database Lookup Step Performance Degraded in 5 vs 4.4.0.2

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Severity: High
    • Resolution: Fixed
    • Affects Version/s: 5.0.3 GA
    • Fix Version/s: 5.1.0 GA
    • Component/s: Step
    • Labels:
      None
    • Story Points:
      5
    • PDI Sub-component:
    • Notice:
      When an issue is open, the "Fix Version/s" field conveys a target, not necessarily a commitment. When an issue is closed, the "Fix Version/s" field conveys the version that the issue was fixed in.
    • Sprint Team:
      Pervach

      Description

      Database lookup step with enable cache for large data sets is substantially slower in 5 vs 4.4. Pentaho 5.0.1-Stable was used, as well as 5 trunk as of today. Comparing against 4.4.0.2.

      Attaching two transformations, one to populate sample data, the other to test the database lookup speed.

      I tested with Postgres 9.3 and SQL Server 2008 R2.

      PostgreSQL ( 23x slower)
      DB Lookup in 4.4 takes 55 seconds ~ 74,000 r/s
      DB Lookup in 5 takes 1380 seconds ~ 2,897 r/s

      SQL Server (26% slower)
      DB Lookup in 4.4 takes 270 seconds ~ 14,709 r/s
      DB Lookup in 5.0 takes 341 seconds ~ 11,701 r/s

        Attachments

        1. LKP.png
          LKP.png
          41 kB
        2. perfomance.png
          perfomance.png
          180 kB
        3. T_DBLOOKUP_PERFTEST_DATA.ktr
          21 kB
        4. T_DBLOOKUP_PERFTEST.ktr
          12 kB

          Issue Links

            Activity

              People

              • Assignee:
                ihar_chekan Ihar Chekan (Inactive)
                Reporter:
                btrost William Trost
              • Votes:
                0 Vote for this issue
                Watchers:
                8 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: