Data loading issue with package

[Tool_Tip]

Dear Experts,

We are using ORACLE as our data source. When we run the oracle package into oracle it takes less than minute

while running the same package from qliksense it takes more than an hour.

Any solution?

[Or]

Investigate the components on the package. Find out which part(s) are slow. Refactor those parts as required. This question is too generic to suggest anything specific.

[Tool_Tip]

Hi,

Thank you for your response. But for your information there is nothing more to investigate, it is just 3-4 line of code where we are passing single parameter into single function and pulling only 2 columns out of it.

In Oracle it is executing same code within seconds.

[Or]

You could run detailed audits on the Qlik side - see https://support.qlik.com/articles/000043270 . On the database side, speak to your DBA and see if there are any waits or anything else that is impacting the result. This might be something related to environment variables, since you're using a different environment when you connect from Qlik. Otherwise, you could try tweaking the ODBC settings, but I can't offer any specifics.

Sounds like the easiest approach might be to just not use the package and write out the SQL instead. It may be sweeping the issue under the rug, but if it works, that's problem solved.

[Tool_Tip]

Writing Simple SQL query if that's simple then I could have achieve this. We can not write because there are so many functions we are calling inside this package...

[BuildItStrong]

I have not worked in Oracle for a long time, so take that into account when you read this reply..  Typically if you load QlikSense from a package or procedure, that package/procedure runs in Oracle and returns a result set.  Syntax will look something like this:

Connect <Some Oracle connection via ODBC or OLEDB>;

Load *;

SQL call owner.package_or_procedure_name('parameter1','parameter2',etc.);

Executing the package or procedure this way causes the package/procedure to be executed in Oracle, and Oracle returns the result set to QlikSense via the load statement.  Because this calls the native Oracle package/procedure, it runs in Oracle, and returns the result set as quickly as it would if you run it in Oracle (because it is run in Oracle).  But... Under certain conditions Oracle packages or procedures can't be called for execution from Qlik Sense.  

Trying to reproduce the sql of a package/procedure as SQL inside Qlik is not always possible, and not always performant.  Depending upon the nature of the SQL, external calls to Oracle may cause Oracle to ignore indexes and perform full table scans.  This can drastically slow the performance.

One solution is to set up a job in Oracle to run the package/procedure on some scheduled interval and store the results to a table in Oracle.  Inside Qlik Sense, one would simply pull the data from the result table.  This works if you can control the parameters of the package/procedure in a meaningful way (so the result table is not too large / doesn't impact Oracle performance) and your business need will allow data that is a little stale (the data is not accurate as of "right now").  There are other advantages to this approach: This approach helps you keep a single source of truth in your data, so that other processes outside of Qlik that rely on this package/procedure will have the same source of data as Qlik Sense, and you have one place to maintain the processes that generate that data.

Ultimately developing good Qlik Sense code requires you to be aware of and optimize database performance, data transer bandwidth, Qlik Sense load and display performance while trying to maintain a single source of truth and governance that controls access to that data.

[BuildItStrong]

If you are calling the package so that it executes in Oracle, are you testing the package in Oracle with the same user that you are using inside Qlik to connect to Oracle?  If not, do the users have different session limits, or could the user connecting from Qlik be running out of sessions?  Does the user connecting from Qlik have CPU usage limits?

[Tool_Tip]

Yes. Same user we are using to connect oracle.

[BuildItStrong]

Check to see if you are running out of sessions.  If  you have a number of calls to Oracle in the load script, move this load task earlier in the script and see if it runs quickly if executed earlier.

[Tool_Tip]

How I can check this??