DSXchange

I am tryin to load data using ODBC stage. I am wusing Upsert mode and what i realise is its extremely slow some times close to 37 rows per second is there any way i can improve the speed

Thanks

Shyam

Is 'upsert' really necessary? Can you use a native Enterprise stage instead? Would help to mention the target db as well.

Yep, I need to capture rejects so i am using upsert. The target is ORACLE 11g Since we dont have drivers we are using ODBC

Shyam

Are you doing the upserts in the 'right' order, meaning whichever action has the best chance of succeeding first? For any updates, are you updating on indexed columns?

Actually all are inserts because i need to capture rejects. I am using upsert, As reject option is not available in Write

Shyam

Okay... Array Size?

60000

Seems... high. What is your ARL, average record length? Does it help if you drop it down to a smaller number, say like 500 or 1000?

Its barely 7-8 columns which amounts to 100 -150 bytes

Still... did you try other values?

Yeah,

The record length is really small. Its extremely slow ...I have no idea what to do..please suggest to improve the performance

Shyam

Is obtaining the Oracle drivers so much of a hurdle? Strikes me you'd be better off doing what Craig originally suggested and use a native Enterprise stage as opposed to ODBC, if performance is your 'driver' (sorry!) :D

Though you say "all are inserts", is that just by nature of your processing, or have you actually set "insert only" or "insert then update" on your upsert mode?

Insert Only

I am trying to insert in to Oracle 11g. Thts the reason we are using ODBC

So there are no drivers for Oracle 11g then?