Loading with FirstSortedValue caused memory error/slow down

Thu, 01 Mar 2012 17:13:04 GMT

The following code caused an out of memory error on QV8.5, and caused a load to chug for over 30 minutes on QV10 when faced with 9 million records:

NOCONCATENATE LOAD

PatientID,

FIRSTSORTEDVALUE(LabStaffID, -(ReportDate & num(mod(LabResultID,10000), '0000'))) as LabStaffID,

LabComponentName,

LOINCID,

date(max(ReportDate)) as LabResultDate,

FIRSTSORTEDVALUE(ResultValue, -(ReportDate & num(mod(LabResultID,10000), '0000'))) as LabResultValue,

FIRSTSORTEDVALUE(LabResultID, -(ReportDate & num(mod(LabResultID,10000), '0000'))) as LabResultID

FROM $(vQVDPath)\iClinicalsLab.qvd (qvd)

GROUP BY PatientID, LabComponentName, LOINCID;

Basically, I was looking to get the most recent lab result for each type of lab for each patient. I ended up rewriting this to break it up which yielded almost the same results (it does the group by to just get the most recent date, then inner joins it to the rest of the laboratory values):

LabTEMP:

NOCONCATENATE LOAD

PatientID,

LabStaffID,

LabComponentName,

LOINCID,

date(ReportDate) as LabResultDate,

ResultValue as LabResultValue,

LabResultID

FROM $(vQVDPath)\iClinicalsLab.qvd (qvd);

MAXTEMP:

NOCONCATENATE LOAD

PatientID,

LabComponentName,

LOINCID,

date(max(LabResultDate)) as LabResultDate

RESIDENT LabTEMP

GROUP BY PatientID, LabComponentName, LOINCID;

INNER JOIN ('LabTEMP') LOAD

PatientID,

LabComponentName,

LOINCID,

LabResultDate

RESIDENT MAXTEMP;

DROP TABLE MAXTEMP;

But the whole process got me thinking about load times and GROUP BY clauses. I've fixed the issue while only guessing at the root of the problem. Can anyone help me understand this? I suspect it was the number of FIRSTSORTEDVALUEs within the single load that caused the problem. Is that unique to FIRSTSORTEDVALUE, or would I run into this problem with any aggregate function? Is it the number of aggregate functions that made it so unhappy? I haven't seen any of my other code choke like it did on this, but there's pressure to make these documents load as fast as possible, and I want to eliminate any obvious slow-downs.

When I have more time, I'll play with some of the code. But I figured I'd float the question out there, in case someone with more experience would know off the top of their head.

Thanks.

Loading with FirstSortedValue caused memory error/slow down

Fri, 04 May 2012 14:56:47 GMT

Shurley,

I am experiencing the same thing. It might be a threading issue related to aggregate functions:

http://community.qlik.com/thread/50589

But not sure how to get around it. This post mentions that the DISTINCT clause causes aggregate functions to become sinlge-threaded, but I am not using DISTINCT and neither are you, so I don't know...

topic Loading with FirstSortedValue caused memory error/slow down in QlikView

Loading with FirstSortedValue caused memory error/slow down

Loading with FirstSortedValue caused memory error/slow down