Results 1 to 4 of 4

Thread: Performance of Microsoft Attunity Connector for Oracle

  1. #1
    frederico_fonseca is offline Junior Member
    Join Date
    Apr 2012
    Posts
    6
    Rep Power
    0

    Default Performance of Microsoft Attunity Connector for Oracle

    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


    Sample package
    flat file, comma delimited with 1152160 records, record size 610
    20 date transformations, 92 columns, mixed datatypes

    Server config
    Microsoft Attunity driver 1.1 and driver 1.2 same behaviour.
    Oracle client 11g 64 bit
    SqlServer 2008R2
    Windows server 2008
    64 GB Ram
    24 Cpus
    1Gbit Network

    Oracle database
    11G (Windows) and 10g (Unix)
    Windows machine (32 bit)
    4 Cpus
    4 GB ram
    Unix Machine (64 bit)
    8 Cpus
    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

  2. #2
    Join Date
    Sep 2006
    Posts
    228
    Rep Power
    10

    Default

    Frederico,

    The results are indeed a little surprising but if I followed your message correctly, you are able to pull an end-to-end copy from a flat file (or SQL Server) to Oracle in about 100 seconds when going via SSIS and the Attunity Connector vs. 100 seconds for standalone loading of a file to Oracle via SQL*Loader. So you more or less get the same performance via SSIS as you get via the highly optimize SQL*Loader - is there a problem now or are you just wondering why the fast load option (Oracle Direct Path) is not showing better numbers?

    Other observations:
    - The network utilization difference may be a hint.
    - What was the network utilization with the SQL*Loader? What options were used?
    By Dror Harari

    To Find Out more About Attunity Technology:
    Attunity
    or:
    Contact Us

  3. #3
    frederico_fonseca is offline Junior Member
    Join Date
    Apr 2012
    Posts
    6
    Rep Power
    0

    Default

    DrorHarari,

    Edit - Small correction sqlloader timing were 80 seconds, not 100.

    Yes I am mainly wondering why the fast load option is performing way worst than the array mode, and looking to see if there is something I should be doing different on the settings or other configuration. Not that there are many settings that can be changed on the SSIS connector.
    Based on my prior experience direct path was always faster for big loads.

    Problem with array load is that is has bigger impact on the Oracle side, specially taking in consideration that we will be running a few of these jobs concurrently. (we have a total of 100 tables to load daily)

    Network utilization -
    All tests were done at different times of the day, including hours where there is little trafic going on, including at a weekend. (no backups running!!).

    The timings/network usage in all cases were similar and constant throughout all runs (and they were a lot of them).
    e.g.
    fast load - 2.2 %
    Array load - 4-5%
    Sql*Loader - 5-6%

    sql loader options
    OPTIONS(DATE_CACHE=20000,ERRORS=99999999,rows=2000 0,readsize=7420000,streamsize=22440000,COLUMNARRAY ROWS=20000,SILENT=(HEADER),DIRECT=TRUE)

    Tried a few loader options but wasnt too worried about optimizing this one as with minimal tuning it was behaving at acceptable levels
    Last edited by frederico_fonseca; 04-20-2012 at 06:40 AM.

  4. #4
    frederico_fonseca is offline Junior Member
    Join Date
    Apr 2012
    Posts
    6
    Rep Power
    0

    Default

    any ideas?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257