ODBC Stage Speed

sgubba · Post by **sgubba** » Fri May 29, 2009 9:02 am

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

chulett · Post by **chulett** » Fri May 29, 2009 9:09 am

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

sgubba · Post by **sgubba** » Fri May 29, 2009 9:20 am

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

chulett · Post by **chulett** » Fri May 29, 2009 9:25 am

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?

sgubba · Post by **sgubba** » Fri May 29, 2009 10:07 am

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

Shyam

chulett · Post by **chulett** » Fri May 29, 2009 10:11 am

Okay... Array Size?

sgubba · Post by **sgubba** » Fri May 29, 2009 10:22 am

60000

chulett · Post by **chulett** » Fri May 29, 2009 10:46 am

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?

sgubba · Post by **sgubba** » Fri May 29, 2009 12:36 pm

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

chulett · Post by **chulett** » Fri May 29, 2009 1:04 pm

Still... did you try other values?

sgubba · Post by **sgubba** » Fri Jun 19, 2009 8:15 am

Yeah,

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

Shyam

miwinter · Post by **miwinter** » Fri Jun 19, 2009 8:21 am

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?

sgubba · Post by **sgubba** » Fri Jun 19, 2009 9:12 am

Insert Only

sgubba · Post by **sgubba** » Fri Jun 19, 2009 9:14 am

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

miwinter · Post by **miwinter** » Fri Jun 19, 2009 9:20 am

So there are no drivers for Oracle 11g then?