QlikView & Oracle Performance over WAN - Qlik Community

Report Inappropriate Content · ‎2015-11-23

I'm curious if anybody has any suggestions for this.

I have an Oracle database that sits across the WAN against which I need to run reports. And it is excruciatingly slow to run these things. When I run the queries against the database locally, it finishes in approximately 10 minutes. When I run it across the WAN, it finishes in approximately 3.5 hours.

Now the first thing that seems obvious is that the connection between locations is too slow, right? Well it's a 500 mbps link, and when I transfer a 350 MB file between file servers across the WAN, it completes in under 6 seconds. The report that I'm trying to run is doing a straight table extract, and the QVDs its generating total to about 500 MB. The largest is 127 MB. So 3.5 hours seems absurd for the volume of data.

I'm kind of scratching my head as to why I see such a massive performance difference when querying a database versus just straight copying data across the network. I'm assuming it has something to do with the way the Oracle client is used in communciation.

Has anybody encountered anything like this? Are there maybe some tweaks that can be made to the Oracle client config to speed things up? Or other suggestions for random things to try to see if there's any difference?

Any help is MUCH appreciated!! Thanks!

Anonymous · ‎2015-11-23

Can you monitor the WAN transfer rate it sustains ?

maxgro · ‎2015-11-23

- try to get all records with a different tools (toad, sql plus, etc....) but the same odbc / oledb setup and compare the time

- maybe an option to have a qlikview on the other (oracle) side and transfer the compressed qvd?

Anonymous · ‎2015-11-23

Rates of compression of around 10:1 aren't unheard of, so if you're ending up with a 500MB file you may be transferring up to 5GB across the network. Regardless, 3.5 hours is excessive. A couple of thoughts:

* When you say run the queries locally, do you mean through an instance of QlikView/Toad on the same machine/LAN as the Oracle DB? If through QlikView on the same machine/LAN, is the query time still 10 minutes?

* Are there any hints in the query that QlikView may not be passing through? (a hint may be ignored without StripComments=0).

* Is your network configured to "downgrade" speeds after a short burst to make sure a user can't saturate the network for a prolonged period of time?

* Are there any keep or where conditions associated with the table? That is, are you retrieving data only to discard it?

* If you run the query with SQL*Plus on the client machine, are the speeds the same? Might help identify the issue as related to QlikView, the ODBC driver, etc.