Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Partner
Partner

Optimize incremental load

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;

 

 

 

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Optimize incremental load

6 Replies
Partner
Partner

Re: Optimize incremental load

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.

Partner
Partner

Re: Optimize incremental load

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
Contributor

Re: Optimize incremental load

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;

 

MVP & Luminary
MVP & Luminary

Re: Optimize incremental load

Channa
Valued Contributor III

Re: Optimize incremental load

can you check this condition

 

where updated>'$(LastLoadLoanTask)';

 

 

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

Channa
Partner
Partner

Re: Optimize incremental load

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..