Dataflow each batch takes longer

  • Jo Pattyn

    SSC-Dedicated

    Points: 31299

    I'm looking for a technical reference on how fast-load work. Each buffer transfer seems to take longer

    Case: Transfer data from Oracle to SQL Server

    Dataflow Source: Oracle Oledb, fetchsize 50000, default buffer max rows 50000

    Dataflow Destination: SQL Server (fast-load), rows per batch 50000, maximum commit size 50000

    The transfers start quick, but as the transfer progresses the average oracle "SQL*Net message from client increases". From 2 seconds per 50000 to 8 seconds per 50000

     

  • Jonathan AC Roberts

    SSCoach

    Points: 16950

    Have you tried setting the fetchsize to the number of rows you are expecting from the query?

  • Jo Pattyn

    SSC-Dedicated

    Points: 31299

    Thanks for the recommendation. In progress of tuning the fetchsize from 50000 (semi-halt at 140 million records) to 10000. 500000 crashes the driver (unexpected termination)

  • heb1014

    Hall of Fame

    Points: 3772

    Have you tried splitting your input stream?  This technique really speeds up the load.

    2019-11-08_13-01-21

     

     

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply