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

[Margaret]

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;

[rwunderlich · Accepted solution]

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

[Margaret]

Thank you, that worked!