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]

Reply via email to