Oracle OLEDB Connect Slow to return results

[johncahillhp]

Hello,

I have a peculiar problem with my Connect SQL results in my QVW. I connect to two different data sources, both Oracle on different servers. The queries to both are very simple. One database takes very little time to return results, while the other takes a very, very long time to return the results when compared to queries done outside of QlikView.

My client config is as follows;

Qlikview for Windows Version 11.20.12235.0 SR5 64-bit Edition (x64)

Oracle OraClient 11g

Windows 7 Enterprise

I've tried several connect statements;

OLEDB Connect TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxx;Data Source=yyy;Extended Properties=""] (XPassword is RBCEAUBOTbYIWQNOILMETZQEDC);

OLEDB Connect64 TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxx;Data Source=yyy;Extended Properties=""] (XPassword is RBCEAUBOTbYIWQNOILMETZQEDC);

Connect TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=xxx;Data Source=yyy;Extended Properties=""] (XPassword is RBCEAUBOTbYIWQNOILMETZQEDC);

All perform the same and take ~ 5 hours to return 79,000 records

Using a SQuirreL client and the Oracle Thin Driver the same query takes 10 minutes...all but 2 seconds to build the output

The query is very basic;

Select * From mytable Where TO_CHAR(Log_TS, 'YYYY/MM/DD') = '2014/09/08'

It has always performed the same, with a painfully long time to load a relatively small number of records.

The other query does not appear to be a problem, so I am ruling out the driver and client config.

The SQuirrel client runs great on the same database, so I am ruling out the Oracle dB and the SQL.

I've ruled out everything, but still can't improve the performance. Where should I be looking?

Thanks in advance,

John

[johncahillhp · Accepted solution]

It would seem that my performance issue is related to CLOB fields. The three CLOB fields were removed from my Qlik query and my performance improved back to expectations.

The comparison between my two different databases was incorrect as both appear to have this same issue. Queries with clients outside of Qlik, ie. SQuirreL perform without issue.

It would appear that the CLOB data in my configuration is the source of the issue.. Qlik takes an inordinate amount of time to process these fields. I have found a local solution to work around the issue.