Improve Attunity driver performance when extracting millions of rows from Oracle

By | March 26, 2016 | 11 Comments
Category: SSIS Tags: , , ,

Attunity driver for extracting data from Oracle is faster but if you are extracting millions of rows from Oracle and you think it is slower than anticipated, make sure you change the following advanced settings

Right click on Oracle Data Source and click on “Show Advanced Editor…”.

oracle-source-right-click
Oracle Source Right Click

Go to “Component Properties” tab and under “Custom Properties” > “BatchSize”.

Oracle Source Advanced Editor
Oracle Source Advanced Editor

The default batch size is 100 which means that as soon as 100 rows are fetched, it will be sent down the pipeline. If you are using cloud environment, sending through pipeline has extra overhead causing performance degradation while fetching millions of rows. Instead, increase the BatchSize value to 10000 and you will feel the performance difference. 10000 is my magic number and worked in all the situations for me but you may have to find your own magic number by experimenting with it


About Vishal Monpara

Vishal Monpara is a full stack Solution Developer/Architect with 13 years of experience primarily using Microsoft stack. He is currently working in Retail industry and moving 1's and 0's from geographically dispersed hard disks to geographically dispersed user leveraging geographically dispersed team members.

11 thoughts on “Improve Attunity driver performance when extracting millions of rows from Oracle

  1. Vishal Monpara Post author

    Hello Nancy,

    I am glad that you had drastic performance improvement. For further improvements, check out the physical distance between multiple servers (source db, destination db, ssis execution server), indexes on source/destination. If destination is db, check the bottleneck for record insertion.

    Regards,
    Vishal Monpara


  2. nancy

    Thanks a ton Vishal .

    I just tried loading like 24.5 million rows and it took only 23.5 minutes and last time it took 4 hours to load. Although I am trying to reduce it further but this is amazing with this small change!!!

    Although my next target is ” load 1 Million rows from Oracle in 30 secs”

    I had been trying to find out more articles on improving Attunity ETLs and this blog was the best and easy one to get..


  3. nancy

    Thanks a lot Vishal. I tried the new query, although it didn’t improve the timing but it actually starting extracting the rows as soon as I started the process. Previously the Data extraction and loading was starting after almost 2 minutes. The query seems to have done that trick. I’ll have to do some reading on why it did that.
    I later changed my defaultbuffermaxrow size to 1000 from 10000 and it reduced the time further by 15 seconds. Can you suggest me anymore properties that can be tuned.


  4. Vishal Monpara Post author

    Hello Nancy,

    Try this query
    SELECT *
    FROM ORDER_DETAIL where UPDT_DT_TM >= TO_DATE(‘2018-07-01’, ‘YYYY-MM-DD’)

    Based on your NLS settings, you may need to tweak your parameters for TO_DATE functions.

    Regards,
    Vishal Monpara


  5. nancy

    Hi Vishal,

    I have to extract almost 300 million rows per table from oracle. I tried using a dataset of approx 6 million rows and it takes almost 5 minutes to extract from oracle (using attunity/OLE DB) and load into sql server. I tried modifying the batchsize using attunity and it reduced the time to 3:30 mints. Can you suggest more ways of improving the speed. I am really new to DWH. I am querying a single table
    “SELECT *
    FROM ORDER_DETAIL where to_char(UPDT_DT_TM,’YYYY-MM-DD’) >= ‘2018-07-01’ ”
    Can you guide me to the right direction.
    Thanks
    Nancy



  6. Vishal Monpara Post author

    Hi Jeffery,

    Good to see a performance improvement with this little setting.

    Regards,
    Vishal Monpara


  7. Jeffrey

    Thanks a Million Vishal,

    It made my day, for me it runs at least 20 times quicker now thanks to this option.
    By default, I couldn’t see the point of using oracle Attunity. I had the same performance as with the Oracle Client connector.
    I changed the BatchSize to 100000 and it’s life changer. I load 1 Million rows from Oracle in 30 secs. Took me 20 mins before.
    Thanks again


  8. Vishal Monpara Post author

    Hi Prashanth,

    There are two things to consider when you want to improve performance.
    1) The query should be optimized (left/right join, where conditions, indexes etc)
    2) As mentioned in this post, change the BatchSize number to 5000/10000/15000 or any other number and experiment with it to find the right number for your environment.


  9. prasanth

    I do have 1 billion records to process from oracle Attunity source whose output is join of 4 large tables.What would the properties be to set in source to extract data faster.


Leave a Reply

Your email address will not be published. Required fields are marked *