topic Re: Performance preceding load on a KEEP in QlikView

Performance preceding load on a KEEP

NickHoff — Tue, 13 Jan 2015 20:57:10 GMT

I've exhausted most of my resources on this issue. The statement below runs correctly, but it takes an extremely long time to run, and we do have some powerful production servers. If I attempt to do a preceding load, and SUM factors i'm still forced to keep Factors in the original load, which un-summed will take just as long to load. If I keep SUM(Factors) i'm forced to do a GROUP BY. What i'm looking for is a solution to improve my load time on a table that contains 500+ million records.

LEFT KEEP(PatientFact)

LOAD PatientDWID,

APPLYMAP('MapRoomType',RevenueCode,NULL()) AS RoomType,

RevenueCode,

RevenueCodeDesc,

ChargeHCPCSCode,

SUM(Factors)

FROM

[..\..\..\1.Common\1.2.QVD\1.2.1.Stage1\1.2.1.Common_RevCodeSummary_CDC.qvd](qvd)

WHERE EXISTS (RevenueCode,RevenueCode)

GROUP BY PatientDWID, RevenueCode, RevenueCodeDesc,ChargeHCPCSCode;

STORE RoomFactors INTO [..\..\5.2.QVD\5.2.2.Stage2\5.2.2.ICU_RevCodeFactorSummary.qvd]

(qvd);

Here is the preceding load i've attempted.

RoomFactors:

LOAD

SUM(Factors) AS FactorsSUM

GROUP BY PatientDWID, RevenueCode;

LEFT KEEP(PatientFact)

LOAD PatientDWID,

APPLYMAP('MapRoomType',RevenueCode,NULL()) AS RoomType,

RevenueCode,

RevenueCodeDesc,

ChargeHCPCSCode,

Factors

FROM

[..\..\..\1.Common\1.2.QVD\1.2.1.Stage1\1.2.1.Common_RevCodeSummary_CDC.qvd](qvd)

WHERE EXISTS (RevenueCode,RevenueCode);

STORE RoomFactors INTO [..\..\5.2.QVD\5.2.2.Stage2\5.2.2.ICU_RevCodeFactorSummary.qvd]

(qvd);

Re: Performance preceding load on a KEEP

Gysbert_Wassenaar — Wed, 14 Jan 2015 10:28:34 GMT

One thing you can try is replacing the WHERE EXISTS (RevenueCode,RevenueCode) with a left keep too. See this discussion: http://community.qlik.com/message/588837#588837

Re: Performance preceding load on a KEEP

jerrysvensson — Wed, 14 Jan 2015 11:03:46 GMT

Since you are doing where exists on fields with the same name, you should remove the first RevenueCode.

Like : WHERE EXISTS (RevenueCode);

This makes a difference.

Re: Performance preceding load on a KEEP

NickHoff — Wed, 14 Jan 2015 15:12:17 GMT

That wouldn't work for what i'm doing because the first RevenueCode is looking everywhere else in the application for RevenueCode, the second matches the first with what's in the current table.

Re: Performance preceding load on a KEEP

NickHoff — Wed, 14 Jan 2015 15:15:00 GMT

I'm already doing a LEFT KEEP on the table with the WHERE EXISTS. I need the WHERE EXISTS because it hits a filter table to limit the results.

Re: Performance preceding load on a KEEP

jerrysvensson — Wed, 14 Jan 2015 15:38:05 GMT

Have you tried doing it in two steps instead of preceeding load?

A couple of days ago I had a similar situation with a preceeding load and aggregation.

It was very slow, I instead used a temporary table and resident load.

I had about 1000 rows so maybe it will not be the same for you.

Re: Performance preceding load on a KEEP

NickHoff — Thu, 15 Jan 2015 20:15:07 GMT

I ended up creating and using a summary table for the rev code with factors summed up. That way performing the aggregation ahead of time I didn't have to use a GROUP by.