Does Resident Table improve performance?

[#1]

Hi,

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

Regards

Raj

[stevedark]

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]

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]

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]

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.....

[#6]

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

[#7]

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 · Accepted solution]

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.

[#9]

Thank you Colin...

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

Raj

[stevedark]

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