Incremental reload problem

[#1]

Hello,

I have a problem when using incremental reload.if the qvd file does not exist at the beginning it's going to be created,then if it exists i reload the data from the qvd file and concatenate new data from the database to the qvd .the problem is that i have to get the data <30 days to the qvd file and >=30 days from the database.im using a comparision after the load statement like

load field1,

       field2,

       date

       where date < 30 days

from qvd

concatenate

load field1,

field2,

date

where date >30 days

from database

the load from qvd works just fine and it's fast,but when i connect to the database to get the data >30 days you can feel it's looking for the last 30 days because the time it takes to get the data is similar to the time when reloading all the table from the database.by the way i'm casting the date field to date after using make date because the date format is not date nor julian so i had to create it myself.

anyone can help??

[#2]

I'm not 100% sure but I don't think the where clause actually makes the reload any faster, it just loads less data.  I have a ~21 million row csv that I load from sometimes and no matter what I do, it takes about 11 minutes for the reload.  It doesn't matter if I have a where clause for a certain day or for 3 years, it takes 11 minutes no matter what.

[#3]

So you mean it doesn't matter if im using a where clause to get less data?it's going to take the same reload time no matter if im using where or not?

if that's the case, do you have any idea how can i use the incremental reload to store the data of today - 30 days in qvd and then get the last 30 days and concatenate them to the table where we loaded the qvd? to be more clear,each day i'm performing a reload and a new day is going to be added to the qvd file.

[#4]

To be honest I'm not sure.  It seems like if that were the case then incremental reload would be pretty pointless but all I can say is what happened in my example which I'm 99% sure is true...

I have to figure something about incremental reload is fast... maybe using NOT exists(some id) in the where clause makes it faster?  Sorry, wish I knew.

[#5]

Hope anyone can help!

[johnw]

Is the where clause in the LOAD or in the SQL SELECT?  Does your database have an index on the date field?

Where Clause In	Index?	What happens
LOAD	N	All rows are retrieved from the database.  QlikView sees all rows, and throws out the unneeded rows as it reads them.
LOAD	Y	All rows are retrieved from the database.  QlikView sees all rows, and throws out the unneeded rows as it reads them.
SQL SELECT	N	Unneeded rows are retrieved and then thrown out by database system.  QlikView sees only the needed rows.
SQL SELECT	Y	Only needed rows are retrieved by the database system.  QlikView sees only the needed rows.

A fast incremental load has the where clause in the SQL SELECT and an index on the field in question.  When loading from the QVD, a fast incremental load does an optimized load by having only a single exists() in the where clause.