is Restructuring of QVD affect performance ?.

[suniljain]

suppose in single table conatain 10 years of data .I have 120 column for 7 years and after that i increases no of column i.e for 3 years no of column is 150.

is restructuring of QVD affect performance ?.

[stevedark]

Hi Sunil,

The biggest issue to look out for is making sure that all of your QVD's load in Optimized (super fast) mode. This will not be the case if all QVDs loaded do not have exactly the same columns in them. Any translation of the data on the way out of the QVD will cause it not to load in Optimized mode - search communities for more information on this.

What I would suggest you do in this instance is to add the extra thirty columns to the old QVDs with nulls or zeros (use zeros for numerics as QlikView is more efficient summing zeros than ignoring nulls). If you can run the creation routine for the old QVDs again it is straightforward to add the new columns at this point. If that is not possible you can create a QVD generate routine that loads from the old QVD's with the extra columns added and then writes new QVD's out again. This second approach will undoubtably be quicker than going back to the original source.

Hope that helps - please post back if you have further questions.

Regards,
Steve

[suniljain]

suppose i donot put extra column for old qvd and directly add new 30 column . then what internal structure look like ?.

[stevedark · Accepted solution]

Hi Sunil,

The danger is that QlikView will try and associate the tables rather than concatenate - this will lead to a very bad synthetic key issue. It will probably actually crash during the load.

You can avoid this by using the "Concatenate (Table1) Load" syntax and QlikView will force the concatenate and effectively populate all the null values for you.

The load from QVD will be much slower if you go this route though - I believe up to 100 times slower. I certainly recommend adding the extra columns to the old QVD's.

Regards,
Steve

[suniljain]

Thank You so much Steve