I’m working with an SQL DB , trying to do incremental load base on field called “Time” , but looks like logic is not working.
Initial load took around 1:30 HRs and even incremental taking almost same time.
Let vQVDCurrentYear = 'D:\qvdev\IT\Innovation SQL'; Let vStart = now(); Let vReloadStart = now();
if filesize('$(vQVDCurrentYear)\IOT.qvd') >0 then
trace >>>>>> $(vQVDCurrentYear)\IOT.qvd ;
MAX_TIME: load max(Time) as LAST_LOAD_TIME from $(vQVDCurrentYear)\IOT.qvd (qvd);
let vLAST_LOAD_TIME = timestamp(peek('LAST_LOAD_TIME',0,'MAX_TIME'));
trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);
drop table MAX_TIME;
LET vLAST_LOAD_TIME = '$(vStartDate)'; trace >>>>>> LAST_LOAD_TIME: $(vLAST_LOAD_TIME);
// load new values IOT: LOAD *; SQL SELECT * FROM kauiotprodeventdb.iot.ProcessEvent WHERE Time > '$(vLAST_LOAD_TIME)' AND AssetSignalTag LIKE 'DB2601,REAL%' OR AssetSignalTag LIKE 'DB2601,STRING456%' OR AssetSignalTag LIKE 'DB2801,W%';
// load prior values if filesize('$(vQVDCurrentYear)\IOT.qvd') >0 then
concatenate(IOT) load * from $(vQVDCurrentYear)\IOT.qvd (qvd);
// store all values store IOT into $(vQVDCurrentYear)\IOT.qvd; drop table IOT;
I suggest to repeat it from the beginning and to look within the document-log which part needs how long. Also monitoring the workload of your machine during the execution may give hints to any bottlenecks and/or parallel running tasks.
Beside this your incremental logic on the timestamp might not be working because there are OR conditions within the where-clause - I assume you missed here the appropriate brackets. A check to the number of records would be useful, too.
Another point is your max(Time) generating which could be moved from the start to the end (before dropping IOT) and could be optimized by reading it from the symbol-table and not from the data-table: fastest-method-to-read-maxfield-from-a-qvd.
A bit contrary to the last suggestion is not to load the timestamp else splitting it into a date- and a time-field. This will require more efforts to create your where-timestamp again but it may reduce the size of your qvd quite significantely which leads to shorter store-and load-times to the filesystem and may increase the performance overall: The-Importance-Of-Being-Distinct