Does Resident Table improve performance?

[#1]

Hi,

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

Regards

Raj

[magavi_framsteg]

Hi.

If loading QVD OPTIMIZED, it is faster than loading from resident tables.
I can't prove it right now, but I see this all the time.
I only do resident loads when necessary.

QlikTech states that QVD:s are optimized for loading fast from them.

[quiquehm]

Hi,

I found a very good article ( a 36 min webex recording plus slides in pdf ) called "Understanding & Best practices with QVD files", by Rob  Wunderlich.

http://qlikviewnotes.blogspot.com/2011/02/qvd-questions-and-answers.html

My understanding is ( maybe wrong ) that due to the QVD files Optimized Load Limitations ( mainly no transformations or new fields allowed ), in case you want to add calculated dimensions or new fields for example when loading a QVD file, a solution could be to load the QVD file as it is ( no manipulations inside, to have an Optimized load ), and then add these new fields in a new table using RESIDENT load ( so the option 2 as commented should be faster ).. Also, should the first loaded table from the QVD file be drop after the resident load ?. Unfortunately I am not an expert developer. Could someone confirm/comment on this ?

Thanks

Enrique

[johnw]

I haven't watched the webex, but you can add fields after a QVD load like this:

MyTable:
LOAD
MyKey
,SomeField
,SomeOtherField
FROM MyFile.qvd (QVD)
;
LEFT JOIN (MyTable)
LOAD
MyKey
,if(SomeField>5,'Big','Small') as SomeFieldSize
,SomeField + SomeOtherField as SomeSummaryField
RESIDENT MyTable
;

You would not drop MyTable in this case because you're joining to it.  I haven't compared the speed to making a new table and then dropping the original.  I'd expect that to use more memory (untested), and we're a bit short on memory on our publisher server, as well as on our development PCs, so I try to conserve it where possible.

[danielrozental]

I did a couple of tests and didn't found much difference in processing time or memory utilization. Tested with 10 Million records, Core i5, 4GB memory.

Doing this took 24 secs

R00:
LOAD ID,
     ShipperID, 
     OrderDate, 
     CustomerID, 
     Discount, 
     ProductID, 
     Quantity, 
     UnitPrice,
     Quantity*UnitPrice AS NetSales,
     Quantity*UnitPrice-Discount AS Sales
FROM
C:\Users\Daniel\Documents\prueba\R00\R00_A.QVD
(qvd);

Doing this took 27 secs

R00:
LOAD ID,
     ShipperID, 
     OrderDate, 
     CustomerID, 
     Discount, 
     ProductID, 
     Quantity, 
     UnitPrice
FROM
C:\Users\Daniel\Documents\prueba\R00\R00_A.QVD
(qvd);
R00_1:
LOAD ID,
     ShipperID, 
     OrderDate, 
     CustomerID, 
     Discount, 
     ProductID, 
     Quantity, 
     UnitPrice,
     Quantity*UnitPrice AS NetSales,
     Quantity*UnitPrice-Discount AS Sales
RESIDENT R00;
DROP TABLE R00;

And this also took 27 secs

R00:
LOAD ID,
     ShipperID, 
     OrderDate, 
     CustomerID, 
     Discount, 
     ProductID, 
     Quantity, 
     UnitPrice 
FROM
C:\Users\Daniel\Documents\prueba\R00\R00_A.QVD
(qvd);
left join(R00)
LOAD
     ID,
     Quantity*UnitPrice AS NetSales,
     Quantity*UnitPrice-Discount AS Sales
Resident R00;

[quiquehm]

Well, it seems there is not significant differences then. Maybe it could depend on the type of calculated field we introduce when loading the QVD.

Not sure if adding a new field using an if statement in your first R00 test ( like the one John adds after the left join )..or maybe adding a WHERE statement would make things slower in the first test. Not sure you could do that test and see what happens ...otherwise I would conclude there is no need to use left joins or resident loads here...the simplest way as I see it would be to add the new/calculated fields when loading the QVD.

What it is not clear to me then is the speed difference between a QVD Optimized Load and a QVD Non Optimized Load ( as if we add fields during the QVD load, it seems as per the QVD best practices slides, we break the rules of the Optimized Load ). Any ideas on this ?

[quiquehm]

Well, it seems there is not significant differences then. Maybe it could depend on the type of calculated field we introduce when loading the QVD.

Not sure if adding a new field using an if statement in your first R00 test ( like the one John adds after the left join )..or maybe adding a WHERE statement would make things slower in the first test. Not sure you could do that test and see what happens ...otherwise I would conclude there is no need to use left joins or resident loads here...the simplest way as I see it would be to add the new/calculated fields when loading the QVD.

What it is not clear to me then is the speed difference between a QVD Optimized Load and a QVD Non Optimized Load ( as if we add fields during the QVD load, it seems as per the QVD best practices slides, we break the rules of the Optimized Load ). Any ideas on this ?

[danielrozental]

I don't believe you can make generelatizations with this, it might be a case where doing a resident load might improve performance, maybe if the table has many fields or if the calculation is more complex, I really don't know.

Loading your QVDs optimized has a huge difference in performance than not loading them optimized. An optimized load is, at least my interpretation, almost like a direct upload to memory where QV only copies information to memory without any processing, while not loading it optimized does cause some processing to be involved.