topic Re: Optimize the group by expression in load script in QlikView

Optimize the group by expression in load script

aj0031724 — Thu, 08 Oct 2015 10:40:21 GMT

Let R = NoOfRows('KPI');
for r=1 to R
LET VKPINAME=Peek('KPI_NAME_GenericKpisActivatedByUser',$(r)-1,'KPI');
LET E=peek('KpiFormula_GenericKpis', $(r)-1,'KPI');

tab:
LOAD DISTINCT '$(VKPINAME)' AS KPI_NAME_GenericKpisActivatedByUser,RNC,SITE,UTRANCELL ,UtranCell_Date,$(E) AS RESULT
RESIDENT UTRANCELLKEY
/* WHERE WildMatch(KPI_NAME_GenericKpisActivatedByUser,'$(VKPINAME)') */
GROUP BY RNC,SITE,UTRANCELL,UtranCell_Date
ORDER BY RNC,SITE,UTRANCELL,UtranCell_Date asc;

team,

Above script is being used to loop over KPI and calculate the KPI VALUE group by RNC,SITE,UTRANCELL,UtranCell_Date .

This is generally taking around 45 minutes to prepare the final "tab" table .

UTRANCELLKEY table has around 20M rows.

Is there any way by which this can be optimized.I need this in load script .

Re: Optimize the group by expression in load script

maxgro — Thu, 08 Oct 2015 11:05:35 GMT

remove order by and distinct

and replace resident with a qvd load

Re: Optimize the group by expression in load script

aj0031724 — Thu, 08 Oct 2015 11:39:31 GMT

Dear Maxgro,

I tried as suggested with below changes in my script:

STORE * FROM UTRANCELLKEY INTO $(GeneralQvd)\UTRANCELLKEY.Qvd;

DROP TABLE UTRANCELLKEY;

tab:
LOAD '$(VKPINAME)' AS KPI_NAME_GenericKpisActivatedByUser,RNC,SITE,UTRANCELL ,UtranCell_Date,$(E) AS RESULT
FROM $(GeneralQvd)\UTRANCELLKEY.Qvd (qvd)
/* WHERE WildMatch(KPI_NAME_GenericKpisActivatedByUser,'$(VKPINAME)') */
GROUP BY RNC,SITE,UTRANCELL,UtranCell_Date ;
/* ORDER BY RNC,SITE,UTRANCELL,UtranCell_Date asc; */

Now the totaltime is 40 minutes saving of 5 minutes from previous scirpt.

Is there any other way around by which we can optimize it to maximum?

Thanks in advance.

Re: Optimize the group by expression in load script

maxgro — Thu, 08 Oct 2015 12:19:25 GMT

I was hoping for a bigger improvement because in the test I did the result was more than 50%

t << AUTOGENERATE(10000000) 19.999.983 lines fetched

***** start resident 08/10/2015 14:08:58

t1 << t 19.999.983 lines fetched

***** end resident 08/10/2015 14:13:26

***** start qvd 08/10/2015 14:13:26

t1 << t 19.999.983 lines fetched

***** end qvd 08/10/2015 14:15:46

Load

TransLineID,

TransID,

mod(TransID,26)+1 as Num,

Pick(Ceil(3*Rand1),'A','B','C') as Dim1,

Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,

Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,

Round(1000*Rand()*Rand()*Rand1) as Expression1,

Round( 10*Rand()*Rand()*Rand1) as Expression2,

Round(Rand()*Rand1,0.00001) as Expression3;

Load

Rand() as Rand1,

IterNo() as TransLineID,

RecNo() as TransID

Autogenerate 10000000

While Rand()<=0.5 or IterNo()=1;

STORE t into t.qvd (qvd);

let v=now(); trace ***** start resident $(v);

t1:

load TransLineID, TransID, count(Dim2)

Resident t

group by TransLineID, TransID;

let v=now(); trace ***** end resident $(v);

DROP Table t1, t;

let v=now(); trace ***** start qvd $(v);

t1:

load TransLineID, TransID, count(Dim2)

from t.qvd (qvd)

group by TransLineID, TransID;

let v=now(); trace ***** end qvd $(v);