I was wondering whether it's possible to append new data rows to a huge qvd file without loading it. I have like 100MB of data which I need to append to a 20GB file and it takes forever to it and my ram is struggling.
I have read something about incremental loading but I'm not sure how to use it. Hope you can help me!
I would not recommend to append data to a QVD rather than creating as many QVDs as you need, and then load them using
LOAD *
FROM Folder\*.qvd (qvd);
Miguel
EDIT: I'm not mentioning buffer loads or partial loads on purpose. They are difficult to manage and anyways, you will have to have the data stored into QVD files (one or more) in the filesystem. Apart from that is that I don't see any particular benefit in performance of doing several small QVDs instead of one large file.
I believe its not possible. Incremental load is to limit the data read from a data source everyday. For example you read data from a Database with 5 million records and store it in a qvd. Tomorrow if 1 million records are newly added then using incremental concept you read only the new 1M records and then append it to the qvd with 5M records. But even in that you have to reload the 5M qvd to append the new data.
You can try making your qvd an optimized load that would be way faster
Try to remove fields that are not relevant
Try to use where exists to pull only data of interest
Thanks for the anwser. Does it make a difference in loading a single huge file or many files which the same size? Obv I can see your point, I can load several small files that I need and don't filter on the huge file by using where or something similar.
Ok, I see. I have my data in qvd for each year and I need to build a huge qvd containing all the data from the beginning. If I have new data for e.g. the last week I will need to load the huge file and concatenate the new data. There is no way to avoid it... Ok, I will try to keep my data slim and optimized. Thanks!
Loading from several files may have its cost, for example, implicit concatenation. But using a WHERE clause over a huge file to determine which rows are loaded also degrades performance.
The biggest advantage of using QVDs is that they store the data so optimized that loading millions of rows will take just a few seconds (superfast optimized load). But when you do any transformation on the fields loaded (e.g.: aggregating sums, adding new fields, creating ranks and fields based on another fields, calculated fields, etc.) all that will make the QVD load not optimized, and therefore not so superfast.
Where is the limit? You will have to consider all those factors and get to your best approach.
As Miguel say, if the qvd, is getting to big to load, is better to split it into some or several separated qvd by some criteria (ie. year, quarter, month, or othes not related to time) and load all or part of them in the documents.
This way you not need to load all the QVD data during update process, only from those QVD's that require it.
In my case, I have splitted the 2 main QVD (fact tables) by month, every day add or update data from the current month and probably from the previous one or two months only, this way the daily updates takes few minutes and not the whole day.
And in the documents, I put a filter by period range, and load only from the qvd of those months, this also reduce, the reload time and total data, of the document, making it more responsive to the user.
.png "Miguel_Angel_Baeyens"){kind=avatar}
