problem in incremental load

[Anonymous]

I have a query regarding executing a script of incremental load. when  1 am  executing the script  its taking time to execute a code.Following is the code :-

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Data Source=192.168.1.108;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=QLIKVIEW;Use Encryption for Data=False;Tag with column collation when possible=False];

//Increment_Sales:

////-------- Start Multiple Select Statements ------

//LOAD

//GoodsSender,

//     InvoiceNumber,

//     CreateDate,

//     ItemNumber,

//     InvoiceQuantity,

//     SalesPrice,

//     LocalAmount,

//     VatAmount,

//    Walkins;

//SQL SELECT  *

//FROM ETPEASV55.dbo."VW_CashOrderTRN1"

////WHERE InvoiceType = '31' and (CreateDate >= 20150101 and CreateDate <= 20161231) ;

////WHERE InvoiceType = '31' and (CreateDate >= 20150101 and CreateDate <= '$(currentdate)') ;

//WHERE InvoiceType = '31'  and CreateDate <= convert(nvarchar(8),getdate()-1,112);

////and  GoodsSender is not null ;

//store Increment_Sales into C:\Users\dmsadmin\Desktop\QlikView_UCB_Report\QVD\extract_qvd\Increment_Sales.qvd;

//drop table Increment_Sales;

Increment_Sales_qvd:

LOAD  GoodsSender,

     InvoiceNumber,

     CreateDate,

     ItemNumber,

     InvoiceQuantity,

     SalesPrice,

     LocalAmount,

     VatAmount,

     Walkins

FROM

(qvd);

Increment_Sales_max_date:

LOAD max(CreateDate) as max_date

Resident Increment_Sales_qvd;

LET vsmaxdate=Peek('max_date',0,'Increment_Sales_max_date');

//DROP Table Increment_Sales_qvd;

concatenate

////Incremental Load only for Insert and Update

Increment_Sales_icr:

LOAD

GoodsSender,

     InvoiceNumber,

     CreateDate,

     ItemNumber,

     InvoiceQuantity,

     SalesPrice,

     LocalAmount,

     VatAmount,

    Walkins;

SQL SELECT  *

FROM ETPEASV55.dbo."VW_CashOrderTRN1"

////WHERE InvoiceType = '31' and (CreateDate >= 20150101 and CreateDate <= 20161231) ;

WHERE InvoiceType = '31'  and CreateDate <= convert(nvarchar(8),getdate()-1,112) and  CreateDate > $(vsmaxdate);

store Increment_Sales_qvd into C:\Users\dmsadmin\Desktop\QlikView_UCB_Report\QVD\extract_qvd\Increment_Sales_qvd.qvd;

drop table Increment_Sales_qvd;

[Anonymous]

thanks

[Anonymous]

This is the following error i am getting stalwar1‌

[sunny_talwar]

Something wrong with the variable may be?

Add trace to check what value does the variable have

Increment_Sales_max_date:

LOAD max(CreateDate) as max_date

Resident Increment_Sales_qvd;

LET vsmaxdate=Peek('max_date',0,'Increment_Sales_max_date');

TRACE $(vsmaxdate);

EXIT SCRIPT;

[Anonymous]

I am getting this value

[sunny_talwar]

May be try with single quotes around the variable

CreateDate > '$(vsmaxdate)';

[Anil_Babu_Samineni]

Probably, doing some Qlik functionality instead DB concepts for picking / storing the max value. It will help you in future for best practice. And, Will you able to tell how much size data hold and RAM size?

[Anonymous]

Sir Its working but taking more than 8 hours to execute the script.

[Anonymous]

Ram size is 8GB

[Anil_Babu_Samineni]

Sunny already point - Could be data size is huge. Let me share my work environment.

Data size -- 20 Billion Rows (5 GB)

RAM - 4 GB

File size shouldn't be less than of RAM memory. So, It takes for me to reload around 6.5 hours. And, After that we have requested to windows team who care of configuration. and they extend to 8 GB. Now, still it is taking 4 hours. For that we need to do some optimization for script to increase performance.

[Anonymous]

so how to do that?