Keeping a counter and increment it via ADF

Question

Keeping a counter and increment it via ADF

sam nick 306

Hello,

I currently have the below data and for the column of Days Active, it is a daily counter that keeps incrementing. I currently employ the methos of nullifying the column and them executing a stored proc. but this process takes around 60 mins to be done.

I tried using ADF too by using a window function on the studentid (also running a pre sql script of nullifying that field). Using this option too, takes 1 hour. The main table has around 3 Million rows

Any recommendations on how to implement this effectively please.

Day1:

User's image

Day 2:

User's image

Day 3:

User's image

Thank you.

Chandra Boorla 12,085 Reputation points Microsoft External Staff

2025-04-28T18:02:56.7233333+00:00
@sam nick

It sounds like the current method, nullifying the DaysActive column first and then reprocessing, is introducing unnecessary overhead, which is likely why it takes close to 1 hour. Here are a few targeted suggestions to help improve the efficiency:

Direct Increment Instead of Nullifying - Instead of resetting the column to NULL and recalculating, a much faster and simpler approach would be to directly increment the DaysActive value by 1 each day. This way, you eliminate the extra step and reduce the overall processing time significantly, you can directly increment the value:

UPDATE YourTable SET DaysActive = DaysActive + 1 WHERE DaysActive IS NOT NULL;

This approach removes an unnecessary step and can significantly speed up the process.

Batch Processing - If needed, consider updating rows in smaller batches (e.g., 100,000 at a time) to reduce locking and resource contention.

Indexing - Ensure there are appropriate indexes, especially if filtering by studentid, though for a full-table update, indexing may have limited impact.

Optimize Stored Procedure - Review your stored procedure for any unnecessary operations or scans that could be optimized.

SQL Server Agent Jobs - If you’re using SQL Server, running this update during off-peak hours through SQL Server Agent can improve overall system performance.

Data Partitioning - For very large tables, partitioning can make maintenance operations like updates more efficient.

Efficient Use of Azure Data Factory - In ADF, prefer using a Script Activity or Stored Procedure Activity to execute the update directly inside the database, minimizing data movement and overhead.

Caution with Window Functions - Window functions are powerful but can introduce unnecessary overhead for a simple increment operation.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.
Chandra Boorla 12,085 Reputation points Microsoft External Staff

2025-04-29T17:00:22.83+00:00

@sam nick

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
sam nick 306 Reputation points

2025-04-30T14:31:25.8966667+00:00

@Chandra Boorla based on your recommendations, i am thinking of creating a temp-table that captures the max of DaysActive and then in the next step just append the value.

Do you recommend that approach and if yes, an ADF job or a stored proc. For either can you provide some assistance on the steps please.
Chandra Boorla 12,085 Reputation points Microsoft External Staff

2025-04-30T17:03:04.1566667+00:00
@sam nick

Great thought — using a temp table or capturing the max DaysActive value is a valid approach if you're applying a uniform update based on that max value (e.g., setting everyone to max + 1). However, if you're simply incrementing each row’s current value by 1, a direct update would be simpler and more efficient.

That said, if your use case needs to reference the maximum value (e.g., to set a consistent new value across qualifying rows), then I’d recommend handling it through a stored procedure rather than an ADF-only implementation. Stored procedures are faster, easier to maintain, and execute within the database itself.

Here's a breakdown of the scenario and the recommended solution:

Create a Stored Procedure in SQL:

CREATE PROCEDURE IncrementDaysActive AS BEGIN SET NOCOUNT ON; DECLARE @maxDays INT; SELECT @maxDays = MAX(DaysActive) FROM YourTable WHERE DaysActive IS NOT NULL; UPDATE YourTable SET DaysActive = @maxDays + 1 WHERE <YourCondition>; -- e.g., IsActive = 1 END;

Note: Replace <YourCondition> with whatever logic you need to determine which rows to update.

Call the Stored Procedure from ADF:

Use a Stored Procedure Activity in your pipeline.

Link it to your SQL database via an existing Linked Service.

Select the IncrementDaysActive procedure.

(Optional) Pass in parameters if needed.

This approach keeps the logic close to the data, avoids extra data movement, and will be far more performant than doing this through a Mapping Data Flow or Copy activity.

I hope this information helps.

Thank you.

1 answer

Your answer

Chandra Boorla 12,085 Reputation points Microsoft External Staff

2025-04-29T17:00:22.83+00:00

@sam nick

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
sam nick 306 Reputation points

2025-04-30T14:31:25.8966667+00:00

@Chandra Boorla based on your recommendations, i am thinking of creating a temp-table that captures the max of DaysActive and then in the next step just append the value.

Do you recommend that approach and if yes, an ADF job or a stored proc. For either can you provide some assistance on the steps please.
Chandra Boorla 12,085 Reputation points Microsoft External Staff

2025-04-30T17:03:04.1566667+00:00

@sam nick

Great thought — using a temp table or capturing the max DaysActive value is a valid approach if you're applying a uniform update based on that max value (e.g., setting everyone to max + 1). However, if you're simply incrementing each row’s current value by 1, a direct update would be simpler and more efficient.

That said, if your use case needs to reference the maximum value (e.g., to set a consistent new value across qualifying rows), then I’d recommend handling it through a stored procedure rather than an ADF-only implementation. Stored procedures are faster, easier to maintain, and execute within the database itself.

Here's a breakdown of the scenario and the recommended solution:

Create a Stored Procedure in SQL:

CREATE PROCEDURE IncrementDaysActive AS BEGIN SET NOCOUNT ON; DECLARE @maxDays INT; SELECT @maxDays = MAX(DaysActive) FROM YourTable WHERE DaysActive IS NOT NULL; UPDATE YourTable SET DaysActive = @maxDays + 1 WHERE <YourCondition>; -- e.g., IsActive = 1 END;

Note: Replace <YourCondition> with whatever logic you need to determine which rows to update.

Call the Stored Procedure from ADF:

Use a Stored Procedure Activity in your pipeline.

Link it to your SQL database via an existing Linked Service.

Select the IncrementDaysActive procedure.

(Optional) Pass in parameters if needed.

This approach keeps the logic close to the data, avoids extra data movement, and will be far more performant than doing this through a Mapping Data Flow or Copy activity.

I hope this information helps.

Thank you.

Answer 1

It is not a good approach to make all values as null and then redefining the values for that DaysActive column.

My suggestion is to create temp table or view on top of the current table. This temp table or view will have only student id and the max value of days active.

If your table looks like this,


| StudentID | ActiveDate | DaysActive |

|-----------|------------|------------|

| AB123     | 2025-05-01 | 1          |

| AB123     | 2025-05-02 | 2          |

| AB123     | 2025-05-03 | 3          |

| AB123     | 2025-05-04 | NULL       |

| BC123     | 2025-05-01 | 1          |

| BC123     | 2025-05-02 | NULL       |

Then create view as below

create view vw_students_temp as
select StudentId,Max(DaysActive) as max_daysactive from Students group by StudentId

This view will give the maximum value of DaysActive for each student.

Then create a stored procedure to update only new rows.

with cte as 
(Select *,row_number() over (partition by Studentid order by ActiveDate) as temp_daysActive from Students 
Where daysActive is null)
update Students
set DaysActive=temp_daysActive+max_daysactive
from  Students 
  join cte on Students.StudentID=cte.StudentID and Students.ActiveDate=cte.ActiveDate
  join  vw_students_temp on Students.StudentID=vw_students_temp.StudentID

Use this update statement in stored procedure. This will update only the new incoming rows and thus it will reduce the processing time.

Share via

Keeping a counter and increment it via ADF

1 answer

Your answer