An incremental load MEANS only loading new data. Only loading new data MEANS you're doing an incremental load.
If your incremental load is taking more time than a full load, it seems likely to me that your incremental load could be written better, or the underlying databases themselves need modifications, such as adding a field or an index to support the incremental load. It is difficult to diagnose without knowing the details of your databases, your indexes, and your incremental load script.
But as some advice for figuring out what's wrong with your incremental load, where is the time being spent? Is it during the read of the database itself? Maybe there's an issue with your SQL (if it's SQL), or the database needs a field like change date to better support incremental load, or needs an index on the fields you're using. Is it during the concatenation of the existing QVD? Are you getting an optimized load? Anyway, figure out what part of the process is going slowly, and figure out how to speed it up, or ask for specific advice on the specific problem.
Hard to tell from your sample qvw as you have edited it from your actual qvw that has the issue. Apart from me having to frig force it to do one off Full Load to create an initial qvd as otherwise it just skips through and does nothing - maybe due to having more IF's than END IF's and some other issues, the incremental load itself does seem to work ok
Would you be able to share the log file from the reload of your real qvw ?
As Bill says, your script does nothing due to various errors, but those errors may only be in your test script.
What you have is likely the most efficient way of getting the max date as it should only have to read the symbol table for the date field, which is relatively small no matter how big the overall QVD is, so that should be fine even though it's unoptimized. I could create an optimized version, but it would likely be slower in practice, or at least no faster.
You're loading in Excel data for step 1. Is that true of the real application? If it's actually from a QVD, for instance, your where predicate will not give you an optimized QVD load, and it would be easy to modify the script to do so - generate a list of dates from the previous max forward, and do where exists on that.
I'm unsure if your concatenate will be an optimized load or not in practice. Depending on how the IDs vs. dates work, another option would be to generate a list of dates prior to the max, and do a where exists on that.
Your sample script sets three variables and exits. The optimized version of your script is therefore three lines to set your variables.
SET vQvdFile='step1.qvd'; SET vTableName='Step1'; LET vQvdExists = 0;
But yes, I realize that's not what you want. I can't really tell what you want, and I know this isn't your real script or loading from your real data source. So...
Attached is a relatively random example of something that kind of looks like maybe what you were trying to attempt to do. Since Date is what seems to distinguish old data from new data, we can load the old data first. We can then get the max date from that in what I believe is the fastest way. Then I'm loading random data intended to simulate your SQL database, and from that taking only what has a date greater than what we already loaded. It just auto-concatenates onto the existing data. Finally, store it.
Here's the script.
LET vMaxDate = num(makedate(2010));
IF filesize('Step1.qvd') THEN Step1: LOAD * FROM Step1.qvd (QVD); MaxDate: LOAD num(max(fieldvalue('Date',recno()))) as MaxDate AUTOGENERATE fieldvaluecount('Date'); LET vMaxDate = peek('MaxDate'); DROP TABLE MaxDate; END IF
// This simulates the SQL to load from your actual database. // You should have an index on the Date field. Step1: LOAD ID ,Date ,Cost WHERE Date > $(vMaxDate) ; // This simulates the current state of your actual database. // Most data will be at or below your max previous and won't be loaded. // The data gets on average a little more recent each time to simulate running this every few days. LOAD num(ceil(rand()*999),'000') as ID ,date($(vMaxDate)+ceil(rand()*400)-390) as Date ,ceil(rand()*1000) as Cost AUTOGENERATE 100 ; STORE Step1 INTO Step1.qvd (QVD);
