MaybeLexter opened a new issue, #9587: URL: https://github.com/apache/hop/issues/9587
### What would you like to happen? #### Current Behavior The existing **Add Sequence** transform supports database sequences, but currently retrieves one sequence value for each input row. For large pipelines, this results in many individual database queries just to generate sequence IDs. #### Problem When processing thousands or millions of rows, retrieving sequence values one at a time introduces unnecessary database round trips and can significantly affect pipeline performance, especially when database latency is high. For example, processing 100,000 rows currently requires approximately 100,000 sequence retrieval queries. It would be more efficient to retrieve multiple sequence values in a single query and use them locally until the batch is exhausted. #### Proposed Enhancement I would like the **Add Sequence** transform to support an optional batch retrieval mode when using database sequences. The proposed enhancement would allow users to: 1. Enable or disable batch sequence retrieval. 2. Configure the batch size, for example 1,000 values. 3. Support Hop variables for the batch size. 4. Retrieve multiple actual sequence values from the database in a single query. 5. Keep the retrieved values in memory and assign one value to each incoming row. 6. Automatically retrieve another batch when all reserved values have been used. 7. Preserve the current behavior when batch retrieval is disabled. For Oracle, the batch retrieval could use a query similar to: ```sql SELECT SEQ_SERVICO_VIGENCIA.NEXTVAL FROM DUAL CONNECT BY LEVEL <= ? ``` The batch size would be configurable rather than hardcoded. Each value must come from an actual database sequence evaluation. The implementation should not assume that sequence values are consecutive or generate additional IDs by incrementing the first retrieved value. #### Expected Behavior With a batch size of 1,000: 1. The first incoming row triggers a query retrieving 1,000 sequence values. 2. The values are stored in memory. 3. Each incoming row receives one reserved sequence value. 4. After consuming all 1,000 values, another batch is retrieved. 5. Processing continues until all incoming rows are handled. For example, processing 100,000 rows would require approximately 100 batch retrieval queries instead of 100,000 individual queries. Unused values from the final batch do not need to be reused. The implementation should also handle concurrent pipeline executions safely, preserving the uniqueness guarantees of the underlying database sequence. #### Use Case I currently use a **JavaScript transform** in Apache Hop to implement this behavior with an Oracle database. The script: - Uses an existing Hop database connection. - Reads the batch size from a Hop variable, defaulting to 1,000. - Retrieves actual Oracle `NEXTVAL` values in batches. - Stores the results in memory. - Assigns one value to each incoming row. - Retrieves another batch only when needed. - Uses `BigDecimal` to preserve numeric precision. This approach avoids executing a separate SQL query for every input row. Having this functionality directly in the Add Sequence transform would eliminate the need for custom JavaScript and make it easier to configure and maintain. #### Benefits - Fewer database round trips when generating sequence IDs. - Better performance in high-volume ETL pipelines. - Less dependency on custom JavaScript implementations. - Configurable batch sizes for different workloads. - Reuse of existing database connection and sequence settings. - Backward compatibility with the current Add Sequence behavior. #### Reference Existing Apache Hop Add Sequence documentation: https://hop.apache.org/manual/latest/pipeline/transforms/addsequence.html The initial implementation could focus on Oracle, with support for other database types where equivalent batch sequence retrieval is available. ### Issue Priority Priority: 3 ### Issue Component Component: Transforms -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
