topic Match function in QV12 slows reload in QlikView

Match function in QV12 slows reload

dafnis14 — Mon, 19 Mar 2018 05:41:04 GMT

Hi,

I have a script similar syntax to the following. After upgrade, it takes 2:15 hours instead of 30 minutes..

LOAD X,

SUM(Y) AS SUM_Y,

MAX(Z) AS MAX_Z

FROM c:\XXX

WHERE MATCH(W, 'AAA','BBB','CCC')

GROUP BY X;

Looks like the combination of the aggregation with the Match causes this.

Any idea why?

Thanks!

Message was edited by: Dafnis X Fixed the Group By clause

Re: Match function in QV12 slows reload

Chanty4u — Mon, 19 Mar 2018 06:42:43 GMT

you can try like

WHERE W like 'AAA','BBB','CCC';

Re: Match function in QV12 slows reload

sasiparupudi1 — Mon, 19 Mar 2018 06:53:36 GMT

LOAD X,D

SUM(Y) AS SUM_Y,

MAX(Z) AS MAX_Z

FROM c:\XXX

WHERE MATCH(W, 'AAA','BBB','CCC')

GROUP BY X,D;

Re: Match function in QV12 slows reload

dafnis14 — Mon, 19 Mar 2018 07:27:21 GMT

Hi Sasidhar,

The original script is correct.

Just fixed my example to the correct syntax.

Another words, there is probably some performance issue with the Group By and Match.

Thanks!

Re: Match function in QV12 slows reload

sasiparupudi1 — Mon, 19 Mar 2018 07:55:18 GMT

Hi Dafnis

Are you loading the data from a qvd? if this is the case, your group by and match will loose the optimization.

I would suggest you load the table in memory and then do the aggregation

ex:

t1:

LOAD X,

FROM c:\XXX;

T2:

LOAD X,

SUM(Y) AS SUM_Y,

MAX(Z) AS MAX_Z

Resident T1

WHERE MATCH(W, 'AAA','BBB','CCC')

GROUP BY X;

This should improve the performance

Re: Match function in QV12 slows reload

dafnis14 — Mon, 19 Mar 2018 08:54:27 GMT

Amazing!

It took less then 5 minutes!

Thanks so much!

Re: Match function in QV12 slows reload

sasiparupudi1 — Mon, 19 Mar 2018 08:57:49 GMT

Great, all the best