topic Re: optimize loading script in QlikView

optimize loading script

Anonymous — Mon, 26 Oct 2015 16:17:55 GMT

The follwoing script took much time to be executed , how coul I optimize it ?

OC:

LOAD [OC] ,DEBUT

,FIN ;

SQL SELECT distinct [OC]

,[DATE DEBUT] AS [DEBUT]

,[DATE FIN] AS [FIN]

FROM [QlikDataWarehouse].[dbo].[OC]

WHERE [Code Article] IS NOT NULL ;

TempVente:

LOAD PrixUnitaireArticle,

PrixArticleHT,

datecol,

PrixRevientTTC

FROM

E:\QVD TEST OC\Vente.qvd

(qvd);

LEFT JOIN(TempVente)

IntervalMatch (datecol)

LOAD DISTINCT [DATE DEBUT]

,[DATE FIN]

resident OC

;

//drop table OC;

Re: optimize loading script

Clever_Anjos — Mon, 26 Oct 2015 16:21:18 GMT

Do you know which step is taking longer times?

Re: optimize loading script

Anonymous — Mon, 26 Oct 2015 16:23:23 GMT

there is a sync table which is created between oc and tempvente , but when I add drop table OC; the code execution time is less than usual but I lost the field oc

Re: optimize loading script

marcus_malinow — Mon, 26 Oct 2015 16:34:35 GMT

IntervalMatch (datecol)

Where is datecol created?

Re: optimize loading script

Anonymous — Mon, 26 Oct 2015 16:36:05 GMT

i update my question

Re: optimize loading script

marcus_malinow — Mon, 26 Oct 2015 16:50:48 GMT

That's better!

Also, in your initial load of OC you have fields DEBUT and FIN, but refer to these as [DATE DEBUT] and [DATE FIN] in your IntervalMatch.

However, after your IntervalMatch

LEFT JOIN (TempVente)

LOAD *

RESIDENT OC;

DROP TABLE OC;

This should be quicker than the generation of a synthetic key.

Re: optimize loading script

PradeepReddy — Tue, 27 Oct 2015 13:46:33 GMT

remove the Left Join condition code in intervalmatch condition and try to load.. it will create a synthetic key, but it is valid one.

LEFT JOIN(TempVente)

IntervalMatch (datecol)

LOAD DISTINCT [DEBUT]

,[FIN]

resident OC ;