Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
tomovangel
Partner - Specialist
Partner - Specialist

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
6 Replies
bramkn
Partner - Specialist
Partner - Specialist

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
Partner - Specialist
Partner - Specialist
Author

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

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

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

- Marcus

Channa
Specialist III
Specialist III

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
tomovangel
Partner - Specialist
Partner - Specialist
Author

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