Load time - Qlik Sense vs Database run time

[jjustingkm]

I am bringing data using two views in the data load script like below. When I run the views in oracle directly each view is taking around 1 minute. But the data load using Qlik is taking around 4-5 minutes. I am not bringing a lot of data in to the memory also ( around 3 hundred thousand)

TABLE A:

VIEW 1 :Select * from A,B,C,D where (A.ID=B.ID AND B.KEY=C.KEY AND D.ID1 =C.ID1)

TABLE B:

VIEW 2

[Or]

300,000 rows can be a fair bit if each row contains a lot of data... try running the same query from e.g. Excel or something else that is external to Oracle, and see how long it takes. If possible, check the execution plan for the query when it runs from Qlik vs. Oracle and see if it gets the same plan or not (and if not, figure out why).

[jjustingkm]

The row contains only 4 column each, let me put it this way. In general , would the qlik sense load takes more time than oracle load? If so, how much difference would it be?

[ArnadoSandoval]

Hi @jjustingkm 

Perhaps your approach is not correct, we do not develop Qlik Application consuming data directly from database or other external sources, usually we extract the data over-night and store it in QVDs, so the dashboards read the data from QVDs, never directly from live databases.

If your solution requires to consume data, real-time from databases, then you need to fine tune your retrieve queries to download new records, sort of incremental load, always saving previously loaded data into QVDs.

I hope my comment make sense to you.

[Or]

There is no 'general' here, as it would depend on your setup, the data, the connection method, etc.

As I suggested, perhaps you would be well-served to look at the execution plan for the query as it runs in Oracle and as it runs in Qlik to see if there are any differences. Typically there won't be but with the Oracle optimizer you never know for sure. Running the same query from another source (using ODBC or JDBC) will also let you see if there's a specific issue with Qlik or if the issue is related to the fact that you're using external connectivity vs. running internally from Oracle.

[jjustingkm]

Sure. And also one more thing I noticed is currently I am using a few parallel hints in my sql . I came to know that

that may not be recognized by Qlik so , I may have to add ++-- something like this to make it work, let me take a 

look at it.

Thanks,

Justin

[rwunderlich]

If your SQL hints are framed in comments, you should set the variable

set StripComments=0;

to instruct Qlik to retain the comments/hints. 

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/SystemVari...

-Rob

[Lisa_Sun]

There few things to check:

1. Are you using native database ODBC driver or Qlik ODBC connector?  You can use native ODBC to do the test firstly. 

2. Where are your database server and Qlik Sense server located?  The network connection needs to take into the consideration.  So the best way is to use EXCEL or other tool to do the same querying and compare the time difference with Qlik Sense. 

3.Try to load two columns only or put the columns name instead of "Select *" in SQL query as it does not use optimal methods on performance. 

    Select * is full table scan, which will not use index, hence more IO cycles are created on DB.

    Select *  also may return more data than required, which in turn will use more network bandwidth. 

    More unnecessary data return will consume more memory as well.   

Hope this helps.

[jjustingkm]

Sure. If I use  StripComments=0; Would it affect the comments in Oracle ? An example line is below.

-- id <> 10

[rwunderlich · Accepted solution]

Check the doc for strip comments, but I believe it only impacts Qlik /**/ and // comments. If your Oracle -- comment is not bracketed by Qlik comments, it won't make a difference. 

-Rob