Optimize incremental load

[tomovangel]

Hello, Guys I am writing incremental load for a table which has 100million records. 

the field im checking updated  is with date and time, first of all I am loading the qvd to get all records, then im geting max value from this qvd, and writing to the sql query where (field)>max value. 

Its working fine, but it takes too much time, to look for the max value. Im going to give you some numbers 

I read the qvd in 10 seconds, then after 120 seconds i get the max value, and after 10 seconds i get all records bigger then mine. 

in total i have 140 seconds, which i think i can optimize with a different approach.

this is my code, I am open to suggestions.  This code is run every 10 minutes..

loan_task:
LOAD
*
FROM [lib://qvd/loan_task.qvd]
(qvd);

MaxDate:
Load
max(updated) as MaxDate
resident loan_task;
let LastLoadLoanTask = Timestamp(peek('MaxDate'),'MM/DD/YYYY hh:mm:ss');
drop table MaxDate;

concatenate
loan_task:
SELECT
*
FROM "public"."loan_task"
where updated>'$(LastLoadLoanTask)';
STORE loan_task into 'lib://qvd/loan_task.qvd';
drop table loan_task;

[bramkn]

I Don't know if it is faster but you can try:
first 1 load
order by updated

you can also just order the table before storing and then get the first record next reload.

[tomovangel]

Unfortunately you can not use order by directly from qvd load, you have to load it and then use Temp table. which i think will increase the load time more..

[santhiqlik]

Hi,

Try retrieving max(Date) from Direct Qvd instead of doing resident load. In your approach, you are trying to calculate max date from all the records in memory and hece it might take time.  Here is the updated code

MaxDate:

LOAD
max(updated) as MaxDate
FROM [lib://qvd/loan_task.qvd]
(qvd);

let LastLoadLoanTask = Timestamp(peek('MaxDate'),'MM/DD/YYYY hh:mm:ss');
drop table MaxDate;

loan_task:

LOAD
*
FROM [lib://qvd/loan_task.qvd]
(qvd);

concatenate
loan_task:
SELECT
*
FROM "public"."loan_task"
where updated>'$(LastLoadLoanTask)';
STORE loan_task into 'lib://qvd/loan_task.qvd';
drop table loan_task;

[marcus_sommer · Accepted solution]

Take a look here: fastest-method-to-read-maxfield-from-a-qvd

- Marcus

[Channa]

can you check this condition

where updated>'$(LastLoadLoanTask)';

as per this condition your QVD has larger update date than ur DB...is it possible?

[tomovangel]

Thank you Marcus for this link, 

I am now trying to make the incremental load based on reload times like in the Qlik Sense help page. 

If i can not do it, i will go back on this in a new thread..