topic Re: optimization qlikview application in QlikView

optimization qlikview application

sat_tok52 — Sat, 20 Feb 2016 06:27:28 GMT

Hi Guru's

Currently my qv application taking around 15 mins to reload,How can I reduce my reload time.

We are using exist,qualify and unqualify, Applymap etc. functions in qlikview and also have unused field,is this functions and fields take more time to reload .

Pls suggest me how can I reduce my reload.

In any help that would be grateful.

Thanks,

Sateesh.T

Re: optimization qlikview application

petter — Sat, 20 Feb 2016 06:49:20 GMT

If you have more information to share it would be much easier to give you specific advice:

1) What kind of source or sources are you drawing data from?

2) How many different tables do you load from? How many columns from each and what kind of columns?

3) Are you doing any SQL as a part of the LOADs? Is it simple SQL SELECT without any joins?

4) Are the SQLs reading entire tables without any WHERE clauses?

5) How is the infrastructure between your QlikView installation and the sources - network bandwidth.

Even if you can't give us answers to these questions - they are factors you should consider or possibly discuss with people that know about these factors in your project.

Re: optimization qlikview application

petter — Sat, 20 Feb 2016 07:01:00 GMT

In general the most effect on reload time comes from:

1) Having a high performant source to read data from

2) Reducing the number of columns that are read

3) Having simple SQL SELECTs that doesn't contain complex JOINS

4) If there are more sophisticated SELECTs being used make sure that the backend SQL DB has proper indexes that supports high-speed querying.

5) Make sure that you only get data that you need. Get rid of the data you don't need as early as possible in your data logistics chain.

a) Leave out columns you don't need immediately and avoid using SQL SELECT * FROM megaTable. (same as 1)

b) Leave out rows you don't need immediately and use for example

SQL SELECT x,y, z FROM megaTable WHERE x=xxx AND y=yyyy

6) Using QlikViews own load script JOINS might be costly in performance if you have big tables that need to be joined.

7) Using ApplyMap() is generally good.

😎 Doing calculated fields in your LOAD statement is not costly - so don't worry too much about that.

9) Reducing the granularity of every column that is read to the necessary resolution can be extremely important.

For instance if you read date-time type of columns and only need to do analysis down to every 15 minutes make

sure that you round-off or truncate the columns that are turned into fields by using Floor(), Ceil() or Round() on them

to leave out unnecessary precision - this can potentially save a lot of memory and increase speed a lot.

10) Find out which of your loads take most time and focus on reducing time for those first.

Re: optimization qlikview application

sat_tok52 — Sat, 20 Feb 2016 07:54:14 GMT

Thanks Petter,

Thanks for your valuable information.

Database:Sql,

connector using:ODBC,

15 qvd's we are using and have where conditions and exist function.

We are using concatenation more than joins.

Thanks,

Sateesh.T

Re: optimization qlikview application

petter — Sat, 20 Feb 2016 08:30:43 GMT

With 15 tables 15 minutes is not necessarily bad. But again that depends one the number of rows there are in each table.

How many rows do you get per minute? Any difference in rows per minute between the tables?

Re: optimization qlikview application

petter — Sat, 20 Feb 2016 08:34:58 GMT

Concatenation should not be a problem when it comes to performance...