QlikView Qlik Sense and Oracle Database - Tips for Performance
Here is a few tips how to load data faster to QlikView or Qlik Sense from Oracle database.
0. Optimize performance of your SQL Statement.
This is really a step 0 and assumes that you have basic knowledge and experience working with Oracle Databases. If you are coming from other RDBMS, you want to take your time and learn how Oracle is different (very much so!)
Explain Plan is your friend - never ever run anything against your production database before you check Explain Plan for your query. One bad query can cause extreme performance degradation for your Oracle database and might impact many people and applications - do not be that guy!
Run and test your query first using Oracle client tool (e.g. Oracle SQL Developer). Monitor execution time, tweak it and once you are happy with it, copy/paste it to QlikView or Qlik Sense.
In other words make sure your query is good before you even touch Qlik tools.
1. Limit data to load.
The more data you load, the slower the process is - common sense, right? If you agree, first thing you've got to do is to say NO to SELECT * FROM table. Always list the column names explicitly in your SELECT statement - you will be surprised how faster your script will run. Do not buy the argument that you do not know all the columns you need upfront - you can always add them later (or never). Actually you will have more troubles with SELECT * and Qlik when columns with the same names in multiple tables will produce synthetic keys.
Always try to filter data in the source (WHERE and HAVING clause) - again the less data to load from a database, the faster your process is. Some people do not realize that if they use preceding load like the one below, Qlik engine would have to bring ALL data first from a database and then filter as second step.
LOAD *
WHERE col1 > 1000;
SQL SELECT
col1,
col2
FROM table1
;
Do this instead:
LOAD *
;
SQL SELECT
col1,
col2
FROM table1
WHERE col1 > 1000
;
Same way, try to JOIN tables in the source - RDBMS engines are extremely efficient doing this job!
2. Use Oracle OLE DB x64 driver.
ODBC driver is slower - use OLE DB instead. Also make sure you use x64 bit driver not x86. Some earlier versions of QlikView had bugs with x64 driver and x86 was faster but it is not the case anymore.
3. Be careful with Query Hints.
Normally Oracle query hints will be written using /*+ ... */syntax. One day I spent many hours trying to figure out why one of my queries took forever to finish if I ran it from QlikView and took only a minute if I ran it from Oracle SQL Developer. Turned out that QlikView by default would strip out comments from SQL statement and treat /*+ ... */ as a comment.
This "feature" is discussed here.
Solution is easy - just use --+ syntax instead if you need to pass a query hint. Of course it is a very easy thing to forget!
Query hint will be stripped by QlikView or Sense:
SELECT /*+ index(e PI_CUST_READMIT_DENOM3) */
...
FROM CUST_V500.CUST_COM_READMIT_DENOM e
....
Query hint will be passed properly to Oracle:
SELECT --+ index(e PI_CUST_READMIT_DENOM3)
...
FROM CUST_V500.CUST_COM_READMIT_DENOM e
....
4. Consider changing default FetchSize.
You can add FetchSize parameter to your connection string and test different values. There is no good value as it depends on your source table row size.
You can make your query run faster by 10-50% if you tweak this parameter. Sometimes it is not worth it but give it a try if it takes too much time to load data from Oracle.
Check these links for more info Link1 Link2 Link3
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=USERID;Data Source=TNSNAME;FetchSize=10000;Extended Properties=""];
I normally like to add DistribTx=0 to a connection string if your Oracle database is running on a version older than 11g. It fixes the issue with chained WITH SELECT clauses (Known as Common table expressions or Subquery Factoring using Oracle's term) - I love them and use them a lot.
OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=USERID;Data Source=TNSNAME;DistribTx=0;FetchSize=10000;Extended Properties=""] (XPassword is JVOCTBVKTCVeFCFIFSUcM);
If you use chained WITH SELECT clauses and do not add DistribTx, you will get this nasty error below:
ORA-32036: unsupported case for inlining of query name in WITH clause
Cause: There is at least one query name which is inlined more than once because it's definition query is too simple and references another query name. This is currently unsupported yet.
Action: remove such query name and retry.
5. Consider Parallel Execution (PARALLEL and PARALLEL(n) hint).
Consider this very powerful but also very dangerous feature - you might want to check with DBAs before you use it. Oracle can run your query using parallel execution feature, ultimately running it in several threads using multiple CPU cores. It is even possible to pass how many threads you request. More on this here - How Parallel Execution Works.
Needless to say, if you do not know what you are doing you can hose the entire system - please consult with DBAs.
SELECT --+ PARALLEL
...
FROM
6. Consider Global Temporary Tables (GTTs).
Get familiar with Oracle Global Temporary Tables. If you run multiple queries against Oracle and you need to reuse the same data or iterate over a data set, one technique is to use GTTs to pre-process some data and then reuse them downstream.
7. Incremental data loads.
If you have too much data to load or your extract takes a lot of time, unfortunately the only option would be incremental load especially if your app needs to be reloaded on a daily basis or even more frequently.
Sometimes it is easier to load data in chunks. For example, you can load one month data at the time and it might be faster and much easier to implement, but nothing really can replace a good incremental process.