Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Experts,
I am generating a repeat sequence using Autonumber function.
AutoNumber(RowNo(), CUST_PROD&REPEAT_BATCH_ID) as REPEAT_SEQ
I have around 30 million records and it takes for ever to run( tested with 1 million records and and took 16 hours). Is there any alternate way to generate this sequence?
Thanks
Aji.
REC_ID | CUST | PROD | CUST_PROD | REPEAT_BATCH_ID | REPEAT_SEQ |
3 | C1 | XYZ | C1-XYZ | 1 | 1 |
4 | C1 | XYZ | C1-XYZ | 1 | 2 |
6 | C1 | XYZ | C1-XYZ | 2 | 1 |
7 | C1 | XYZ | C1-XYZ | 2 | 2 |
8 | C1 | XYZ | C1-XYZ | 2 | 3 |
9 | C1 | XYZ | C1-XYZ | 2 | 4 |
11 | C2 | ABC | C2-ABC | 3 | 1 |
12 | C2 | ABC | C2-ABC | 3 | 2 |
14 | C2 | HIJ | C2-HIJ | 4 | 1 |
15 | C2 | HIJ | C2-HIJ | 4 | 2 |
17 | C2 | XYZ | C2-XYZ | 5 | 1 |
18 | C2 | XYZ | C2-XYZ | 5 | 2 |
19 | C2 | XYZ | C2-XYZ | 5 | 3 |
I would recommend you to change from your autonumber approach to an Peek Previous approach. I think you will find it quite faster.
‐Vegar
Your syntax is incorrect:
AutoNumber(RowNo(), CUST_PROD&REPEAT_BATCH_ID) as REPEAT_SEQ
This numbers the rowno() according to the sequence defined by the concatenated strings. You need this:
AutoNumber(CUST_PROD&REPEAT_BATCH_ID) as REPEAT_SEQ
I would recommend you to change from your autonumber approach to an Peek Previous approach. I think you will find it quite faster.
‐Vegar
A second suggestion is to make your script incremental so you don't need to run 30M rows every time, only the new/changed batches.
Hi Aji,
Maybe try doing the Repeat_Seq at front end and monitor the performance?
Refer qvw attached for the expression used.
Thanks and regards,
Arthur Fong