Data Load from Oracle is Slow

[#1]

Hello all, gwassenaar‌

Currently I am loading a test data from the oracle data mart which contains maximum of 50,000 records through ODBC connector.

I am surprised to see that it is taking more than 4 hours to load these records.

I have never worked with oracle in my past experience. So I have no clue what is going on ?

More information :

QlikView and Database servers are in other continents. I am still figuring how can OLEDB help in this condition.

Any help is really appreciated.

Thanks

BR

Susvith

[Clever_Anjos]

First of all please ask your DBA to check your SQL. Maybe it´s not optmized

[maheshkuttappa]

Also check field Data type , this will slow down the extract (but not 4hrs , there some thing wrong in your SQL or connection)

eg: varchar2(2000) will consume more time then varchar2(100)

change using CAST in SQL

eg: CAST( '22-Aug-2003' AS varchar2(12) )

Go to ODBC administrator change buffer size from 64000 to 200000

[mikibh]

Not sure if you still need help with this but here you go. Do you have any clob data types in your data pull if so you can use: to_char(substr(FIELD_NAME, 0, 3999)) to pull the first 4000 characters and it will be much faster, also the ODBC buffer size setting mentioned before might help especially if your database is in a remote location. (AWS or other cloud locations)

Good luck

Miki