topic AutoNumber(RowNo(),[MyField]) takes too long in App Development

AutoNumber(RowNo(),[MyField]) takes too long

Margaret — Mon, 14 Jun 2021 19:30:50 GMT

I'm using AutoNumber(RowNo(),[MyField]) and it takes 25 minutes with 800k rows and I'm only going to have more as time goes by. (In SQL I'd use Row_Number() over Partition By, but I can't use that here.) How can I accomplish the same thing in substantially less time?

Below is a Load Script that does what I want with some fake data. The point is to have a column that numbers the rows in which a patient visit took place in multiple departments on the same day. For example, a patient is admitted to the Emergency Department (ED) and transferred to the ICU. The ED row is '1', the ICU row is '2'. How can I do this for lots of data very quickly?

TempTable2:
LOAD * INLINE
[VisitNum, Dept, Date,
1000, ED, 2021-06-10
1001, ED, 2021-06-10
1001, ICU, 2021-06-10
1002, MedSurg, 2021-06-11
1003, ICU, 2021-06-10
1003, MedSurg, 2021-06-11
1004, ED, 2021-06-11
1004, MedSurg, 2021-06-11
];

TempTable1:
LOAD
*,
(VisitNum & '-' & Date) as PatientDayPresent
Resident TempTable2
;

DayPresent:
Load DISTINCT
*
,AutoNumber(RowNo(),PatientDayPresent) as PatientDayPresentIndex
Resident TempTable1;

Drop Tables TempTable2, TempTable1;

Re: AutoNumber(RowNo(),[MyField]) takes too long

rwunderlich — Tue, 15 Jun 2021 04:30:21 GMT

AutoNumber can be slow when you have lots of distinct AutoId values. A faster alternative can be to use Previous() and Peek() to process an ordered table.

TempTable2:
LOAD *, RecNo() as RecId INLINE
[VisitNum, Dept, Date,
1000, ED, 2021-06-10
1001, ED, 2021-06-10
1001, ICU, 2021-06-10
1002, MedSurg, 2021-06-11
1003, ICU, 2021-06-10
1003, MedSurg, 2021-06-11
1004, ED, 2021-06-11
1004, MedSurg, 2021-06-11
];

DayPresent:
LOAD
*,
if (Previous(VisitNum) = VisitNum and Previous(Date) = Date, peek('PatientDayPresentIndex') + 1, 1) as PatientDayPresentIndex
Resident TempTable2
Order by VisitNum, Date, RecId
;
Drop Table TempTable2;

Note I assigned a "recId" in the initial load as a way of maintaining the order of the intra-day events.

-Rob

Re: AutoNumber(RowNo(),[MyField]) takes too long

Margaret — Tue, 15 Jun 2021 20:00:48 GMT

Thank you, that worked!