Issues with Autonumber() performance

[ajipaul03]

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

[jonathandienst]

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

[ajipaul03]

Thanks for the quick reply.

Hmm... shouldn't the AutoNumber(CUST_PROD&REPEAT_BATCH_ID) just replicate the REPEAT_BATCH_ID?
I need to sequence the the each batch staring from 1

[Vegar · Accepted solution]

I would recommend you to change from your autonumber approach to an Peek Previous approach. I think you will find it quite faster.

‐Vegar

[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.

[Arthur_Fong]

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