Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Does Resident Table improve performance?

Hi,

Could anyone tell me if the use of resident tables would help improve performance?

Regards

Raj

1 Solution

Accepted Solutions
colinh
Contributor II

Does Resident Table improve performance?

I've just done a quick test and method 2 seems to be faster - 12 seconds vs 20 seconds on 1.5 million rows. This is what I would expect, since in method 2 the QVD load is optimised, whereas in method one you lose the optimisation by doing the where clauses on the QVD loads.

The best way to tell though would be to test on your own data. If you go into Document Settings and set the document to generate a logfile, the logfile will contain the exact start times of each step in the load process as well as the start and finish times of the whole reload, which will show you exactly which statements are faster or slower.

16 Replies
MVP
MVP

Does Resident Table improve performance?

Hi Raj,

It depends entirely on how you use them. They can be very useful in your load script to reduce joins in your eventual data model - if you do this then you can drastically improve performance. However, they can significantly slow your load routine as data needs to be parsed twice. Apparently saving to QVD and loading from file is quicker than a resident load - but I have not personally benchmarked this.

For general performance of documents it is key to reduce number of links. Use of the ApplyMap statement is the best way of doing this. For performance of load script one key factor is use of QVD's (allowing incremental and concurrent loads) and making sure these QVD's load in superfast mode.

Information on all of the above can be found in the QlikView manuals and on QlikCommunity.

Hope that helps.

Regards,
Steve

deepakk
Valued Contributor III

Does Resident Table improve performance?

hi Raj,

Yes . Using resident helps in performance. But use only if there are claulation or conditiion required in table.

eg:

Test:

Load a, b, c from table;

Load a, if(b='A', Yes,No ) type, C resident Test;

colinh
Contributor II

Does Resident Table improve performance?

Hi Raj

Compared to what? What are you trying to do?

If you are doing transformation on a very large dataset, I've found that saving to QVD and then doing transformation by loading from the QVD is significantly faster than using a resident load.

sushil353
Honored Contributor II

Does Resident Table improve performance?

resident table are something which are stored on local so when you working with those they are fast to access and in this way we can say that they can improve the performance.....

Not applicable

Does Resident Table improve performance?

Hi Everyone,

I am using QVD file to load data. Both examples are shown below:

Example1 -

Table1:

Load * from abc.qvd where code=101

Table2:

Load * from abc.qvd where code=102

Now instead of loading data from the qvd twice in the above example should i do as shown in the following example 2 -

Table0:

Load * from abc.qvd

Table1:

Load * resident Table0 where code=101

Table2:

Load * resident Table0 where code=102

Could you tell me which of the above 2 are better for performance?

I hope my question is clear...

Raj

Not applicable

Does Resident Table improve performance?

Hi Sushil,

I am using QVD file to load data. Both methods are shown below:

Method1 -

Table1:

Load * from abc.qvd where code=101

Table2:

Load * from abc.qvd where code=102

Now instead of loading data from the qvd twice in the above example should i do as shown in the following Method 2 -

Table0:

Load * from abc.qvd

Table1:

Load * resident Table0 where code=101

Table2:

Load * resident Table0 where code=102

Could you tell me which of the above 2 methods are better for performance?

I hope my question is clear...

Raj

colinh
Contributor II

Does Resident Table improve performance?

I've just done a quick test and method 2 seems to be faster - 12 seconds vs 20 seconds on 1.5 million rows. This is what I would expect, since in method 2 the QVD load is optimised, whereas in method one you lose the optimisation by doing the where clauses on the QVD loads.

The best way to tell though would be to test on your own data. If you go into Document Settings and set the document to generate a logfile, the logfile will contain the exact start times of each step in the load process as well as the start and finish times of the whole reload, which will show you exactly which statements are faster or slower.

Not applicable

Does Resident Table improve performance?

Thank you Colin...

I will go ahead and change the document settings to generate a log file.

Raj

MVP
MVP

Does Resident Table improve performance?

Hi there,

Looking at your example (loading * from QVD three times) this will not create three tables in the data model as the columns will be identical each time. This will therefore just create a single table with some of the rows duplicated. Is this what you are aiming for?

Regardless of that QVD load instead of Resident will definitely be quicker.

Regards,
Steve

Community Browser