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.
