Posting here to see if you guys have any idea of what may be wrong and even to see if you can give me any pointers of other things to look for.
I am at the moment developing and testing some packages to load data from SQL Server onto Oracle and have bumped into a smallish problem.
All packages are basically the same.
Sql command - truncate oracle table
DataFlow - extract from sqlserver ->derived column ->oracle attunity destination
Several of our extracts are loading ovr 2 million records each, and we have a few tables with 30 to 50 million records each.
Rows per second was exacly the same regardless of the size of the table when I was testing.
To my surprise loading the data using array mode turned out to be significantly faster than using the fast load option.
As I wasn't happy with timing I created a sample package to read from a file instead to see if it made any difference.
Timing from this sample package were the same as those extracting data from Sql Server
flat file, comma delimited with 1152160 records, record size 610
20 date transformations, 92 columns, mixed datatypes
Microsoft Attunity driver 1.1 and driver 1.2 same behaviour.
Oracle client 11g 64 bit
Windows server 2008
64 GB Ram
11G (Windows) and 10g (Unix)
Windows machine (32 bit)
4 GB ram
Unix Machine (64 bit)
24 GB Ram
During tests Sql server machine CPU was around 10% all the time, with 46 GB memory being used
Package running going up to 400MB ram used depending on buffers size defined (see below)
Network usage - 2.2% average for fast load, 4-5% for array load
Loading into the unix server or the windows machine made no difference. Timings were the same in either
Trials done by changing number of records and buffer size on dataflow, and increasing attunity driver values (buffer and batch size/transaction size)
Values for array mode (Fastload = false)
tuning - duration in seconds
default SSIS - 208.886
20k - 105.519
40k - 108.483
values for direct path mode (Fastload = true)
default SSIS -157.826
20k + 128k buffer - 154.878
20k + 256k buffer - 150.915
20k + 512k buffer - 151.259
20k + 1024k buffer - 152.741
20k + 2048k buffer - 158.138
Loading the same file using SQL*Loader, 20k rows batch, datecache=20000, and streamsize of 16m took 100 seconds