Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
quriouss
Creator III
Creator III

Why is Oracle extract so slow?

I'm extracting a table from Oracle - 2.2 million rows, 10 columns, and the extract/load job in Qlik Sense took over 8 hours.

Using the Oracle SQL Developer tool, I downloaded the same data into a csv file in under 30 minutes, then loaded it into Qlik Sense in less than a minute.

Is there a page of tips to speed up the Qlik-Oracle connection? (ODBC via a 64-bit system DSN).

(I think I can probably get away with a single one-off load followed by daily differential loads, but it would be good to have an answer on hand).

20 Replies
Colin-Albert

Is there a more efficient way of loading the data? - Using incremental loads, mapping tables etc.

Do you always need to reload each table? Some tables such as customer lists may be OK to load once a day or once a week depending on how fast your data changes.

Can you export the data to txt or csv files locally and then ftp those to your QlikView server for loading?

Can you setup a local QlikView machine possibly running a desktop version to generate the QVD locally and copy it to the server?

There are many options to optimise loading remote data rather than simply dragging all the data across your network.

Colin-Albert

Do you need to load all fields from the table?

Changing a "select * " to a statement that only loads the required fields can often give a significant performance increase.

Also check that you are not selecting large fields to only use parts of the data in qlik - e.g. selecting a timestamp field only to load just the date part in Qlik - convert the timestamp to a date in the SQL, then less data is transferred across the network.

petter
Partner - Champion III
Partner - Champion III

Oracle ODBC drivers aren't always good - depending on the version. Which version of Oracle are you using on the Oracle DB and which version of the ODBC-drivers are you using on your client?

petter
Partner - Champion III
Partner - Champion III

You should try the free Qlik ODBC Connector Package that contains a high-performance Oracle connector. You can find it and download it from Qlik Market:   http : // market . qlik .com   (remove the spaces).

     Search for "ODBC Connector"

quriouss
Creator III
Creator III
Author

Thanks - I downloaded that and tried it but unfortunately it is slower than the native Oracle driver (only about 10% slower, but still...)

I'm attacking this by incremental loads now, and having some quite good success!

jagan
Luminary Alumni
Luminary Alumni

Hi,

Sometimes ODBC is very slow, you can try using the OLE DB connection instead of ODBC. Also check whether you have issues in the database.  If the database performance is not good then it will impact the load time in Qliksense also.

Regards,

Jagan.

Not applicable

Hi,

I had the same issue, resolved by using an OLEDB driver instead of ODBC

Good luck.

Anonymous
Not applicable

hi guys, check out my post - i hope it will help someone. Some tips about Oracle and QlikView connectivity

http://boristyukin.com/qlikview-and-oracle-database-tips-for-performance/

Amogh
Partner - Contributor II
Partner - Contributor II

Is there any guaranteed solution to this. I also wanted to load 10M rows from the table on daily basis which takes 10 hours.

Martin_S
Contributor
Contributor

I am having a similar issue.

Executing the same query.

In SAS: 23 seconds for 1.2 m rows

in Qlik Sense: I gave up after 45 minutes.