1 Reply Latest reply: May 4, 2012 10:56 AM by Simon Saugier RSS

    Loading with FirstSortedValue caused memory error/slow down

    Sally Hurley

      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.