topic Re: Problem with performance in QlikView

Problem with performance

Anonymous — Thu, 15 Feb 2018 14:36:42 GMT

Hello everyone,

I'm loading a model that is taking a bit (2 1/2 hours). Using trace I've found that the attached piece of code takes 30 minutes to load.

¿What can be slowing it so much?

Any help would be appreciated!

Thanks,

Josefina

Re: Problem with performance

settu_periasamy — Thu, 15 Feb 2018 14:49:01 GMT

Hi,

Try to do the 'Resident' Load instead of Preceding.

Re: Problem with performance

Miguel_Angel_Baeyens — Thu, 15 Feb 2018 15:03:19 GMT

Things I would do:

Replace individual If() by ApplyMap() or JOIN
Do everything at once (if possible) instead of using a preceding load
If the table is big, store it on QVD format, then load from there
Use flags instead of creating new fields, then use the flag in set analysis, when possible

So the script would look like:

STORE Traffic_tmp INTO Traffic_tmp.qvd (qvd); // place your LIB: connection here instead

DROP TABLE Traffic_tmp;

Traffic:
LOAD
Traffic_Date,
Phone,
Pick(Match(Left(PrepType, 1), 'P', 'Q'), 'Prepaid', 'Pospaid') AS [Prepaid / Pospaid],
ApplyMap('TechnologyMapping', AutoNumber(Phone & '_' & TrafficDate), 'Null') AS [Eq Real Technology],
// be careful with this one, AutoNumber() works differently with each table,
// I'd suggest to create the number and do a JOIN or an ApplyMap() instead of this
TotalSMSQty,
TotalPremiumSMSQty,
TotalMin,
TotalVolumen,
Type
FROM Traffic_tmp.qvd (qvd); // place your LIB: connection here instead

I wrote it on the fly, check for missing commas or parentheses.

Re: Problem with performance

Anonymous — Thu, 15 Feb 2018 16:10:21 GMT

Hi, thank you for your detailed answer. I am suprised by this

"If the table is big, store it on QVD format, then load from there"

¿Is it faster if a table loads from a QVD than from memory (resident)? ¿If I already have it on memory how can this help?

I'd love if you could explain it to me further.

Many thanks!

Re: Problem with performance

lorenzoconforti — Thu, 15 Feb 2018 16:20:31 GMT

From what I can see the factor slowing it down the most is the Applymap; try to take it out and see if performance improves. If so, replace it with a Join

Re: Problem with performance

Anonymous — Mon, 19 Feb 2018 07:27:38 GMT

I think Autonumber is slowing down reload, try by Keeping Autonumber and Applymap in separate loads. Create Autonumber field first and Use applymap in Preceding load. hope this helps.

Re: Problem with performance

Miguel_Angel_Baeyens — Mon, 19 Feb 2018 07:33:23 GMT

Yep, sounds counterintuitive but experience proves this point: QVD optimized is faster than RESIDENT, especially for big tables. I don't know how each of them work in the source code, but we have an application loading a single 12GB QVD, which works, but the same RESIDENT table simply doesn't.

Re: Problem with performance

Anonymous — Mon, 19 Feb 2018 17:08:44 GMT

Thanks for the explanation!