Incremental Load on Date condition

[Sean_BI]

Hello, 

I am performing a very simple incremental load in qv. However, to return max date from .qvd and select records from database is becoming expensive. Is there another way to tune my script.

Step1:

Temp:

Load max(period) as Period_Max from ..\sales_order.qvd; 

//The scripts is taking too long to return max(period) near about 20 min. The qvd size is 10 GB. So, i think reading from qvd should be faster.

Step2: 

//declare variable to hold Period_Max 

Let vMaxdate = Date(peek('Period_Max',0,'Temp'),'DD-MMM-YYYY');

Drop Temp;

Step3:

Sales:

select * from dbschema.SalesOrders_DLY where PERIOD > '$(vMaxdate )';

// this command takes almost 1 hr to //fetch 2.5 lac rows only. This appears to be expensive as similar data read is //faster in other scripts that are currently exists. Indexing on dbschema.SalesOrders_DLY is done date field.

// the datatype for PERIOD in SalesOrder_DLY database table is 'DD-MMM-YYYY'

CONCATENATE

LOAD * FROM ..\sales_order.qvd;

STORE Sales into ..\sales_order.qvd (qvd);

DROP Sales;

Can this be rewritten to with EXISTS statement or any other optimized way especially on date field condition?

Thank you!

Sean.

[Or · Accepted solution]

I think the easiest approach here would be to store the Max(Date) value from the SQL outside of the large QVD, for example in a different QVD. Then when you are ready to run again, you use that value instead of reading the entire QVD just to find the max value.

After reading Sales:

MaxDate:

Load Max(Date)

Resident Sales;

Store MaxDate into {blah};

Insofar as the SQL query, that's a question to take up with your DBA. You may be able to improve performance either by tweaking your query to use existing indexes, or by adding indexes. The amount of time taken here shouldn't have anything to do with Qlik.

[marcus_sommer · Accepted solution]

The suggestion from Or could be even speed up by reading the system-table and not the data-table - here an example how it worked: https://qlikviewcookbook.com/2013/09/fastest-method-to-read-maxfield-from-a-qvd/ 

- Marcus