Problem with performance

[Anonymous]

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

[settu_periasamy]

Hi,

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

[Miguel_Angel_Baeyens]

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.

[Anonymous]

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!

[lorenzoconforti]

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

[Anonymous · Accepted solution]

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.

[Miguel_Angel_Baeyens]

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.

[Anonymous]

Thanks for the explanation!