What's the best way to transform this WHILE loop SQL Server payment calculation into bulk operations?

Data and Databases
1

I have a big performance problem with my SQL Server stored procedure. It uses a WHILE loop to handle payment calculations and it’s super slow.

The procedure processes payment data by looping through each batch_num in a temp table called payment_batch_temp. For each batch, it does premium calculations, updates the policy_transaction_main table, and inserts records into payment_processing_log.

The main issue: Processing 300K policies takes 15 hours because of the row-by-row approach and lots of subqueries in the loop.

What the loop currently does:

  • Goes through each batch_num in payment_batch_temp
  • Calculates premium amounts and applies business rules to update policy_transaction_main
  • Inserts results into payment_processing_log
  • Updates processed_flag in both payment_batch_temp and policy_transaction_main
  • Sometimes creates new rows in policy_transaction_main for leftover premium amounts

My main question: How can I convert this whole thing to work with sets instead of loops while keeping the exact same logic?

Requirements:

  • Final results must match exactly what the loop produces
  • Main tables: payment_batch_temp, policy_transaction_main, payment_processing_log, payment_source_temp, policy_data_temp
  • The payment_batch_temp table groups payment records by batch_num
  • Each batch_num represents a group of records processed together

I’m looking for CTE examples, bulk update patterns, or any proven methods to eliminate this loop bottleneck.

truncate table payment_batch_temp

insert into payment_batch_temp WITH(tablock)(contract_id,premium_amount,transaction_id,process_date,created_date,batch_num,system_id,agent_id,original_transaction_id,processed_status,policy_updated_flag,manual_entry_flag,premium_type,coverage_item,coverage_location,wind_coverage,endorsement_flag,contract_year,reinsurance_date,custom_field5)
select contract_id,premium_amount,transaction_id,process_date,created_date,row_number() over (partition by contract_id,system_id order by effective_date asc, manual_entry_flag desc,coverage_item,premium_amount desc,transaction_id) as batch_num,system_id,agent_id,original_transaction_id,0,policy_updated_flag,manual_entry_flag,premium_type,coverage_item,coverage_location,wind_coverage,endorsement_flag,contract_year,reinsurance_date,custom_field5
from (
select distinct p.contract_id, p.premium_amount, p.transaction_id, p.effective_date as process_date,p.created_date,p.system_id
    ,case when p.agent_id is not null and p.agent_id <> 0 then p.agent_id else m.agent_id end as agent_id
    ,p.original_transaction_id
    ,p.effective_date
    ,p.manual_entry_flag
    ,p.policy_updated_flag
    ,p.transaction_attribute_3 as premium_type,p.coverage_item,p.coverage_location,p.wind_coverage,p.endorsement_flag,p.contract_year,p.reinsurance_date,m.custom_field5
from payment_source_temp p with (nolock)
JOIN policy_transaction_main m with (nolock)
ON m.contract_id = p.contract_id
and m.system_id = p.system_id
and (isnull(m.premium_type,'STANDARD')) = isnull(p.transaction_attribute_3,'STANDARD')
where (
    (m.transaction_type_code <> 4 and 
        (cast(p.effective_date as date) <= cast(m.process_date as date))
    )
    OR
    (isnull(p.manual_entry_flag,0) = 0 and m.transaction_type_code = 4 and (cast(p.effective_date as date) < cast(m.process_date as date)))
    OR
    (isnull(p.manual_entry_flag,0) = 1 and m.transaction_type_code = 4 and (cast(p.effective_date as date) <= cast(m.process_date as date)))
)
and m.record_type = 2
and m.batch_id = @current_batch_id
and isnull(p.processed_indicator,0) = 0
and sign(p.premium_amount) = 1
)a

SELECT @max_batch = max(batch_num), @min_batch = min(batch_num) from payment_batch_temp with (nolock)

WHILE (@min_batch <= @max_batch)
BEGIN

UPDATE target SET 
    target.commission_premium = CASE WHEN calc.has_payment = 1 THEN
        isnull(target.commission_premium,0) + 
        CASE WHEN row_rank = 1 then calc.calculated_commission + 
            CASE WHEN calc.payment_amount > calc.total_amount then 0 
            else calc.balance_adjustment end 
        else calc.calculated_commission END 
    ELSE isnull(target.commission_premium,0) END
    
    ,target.outstanding_premium = CASE WHEN calc.has_payment = 1 THEN CASE WHEN calc.row_rank = 1 THEN calc.calculated_outstanding - calc.balance_adjustment ELSE calc.calculated_outstanding END ELSE target.outstanding_premium END
    ,target.processed_status = CASE WHEN (CASE WHEN calc.row_rank = 1 THEN calc.calculated_outstanding - calc.balance_adjustment ELSE calc.calculated_outstanding END) = 0 AND calc.total_amount <> 0 AND calc.has_payment = 1 THEN '1' ELSE CASE WHEN calc.total_amount <> 0 AND calc.has_payment = 1 THEN '2' ELSE 'N' END END
    ,target.collected_premium = CASE WHEN calc.has_payment = 1 THEN target.term_premium - calc.calculated_outstanding ELSE target.collected_premium END
    ,target.has_payment_flag = CASE WHEN calc.has_payment = 0 THEN 0 WHEN calc.total_amount <> 0 THEN 1 ELSE 0 END
    ,target.payment_process_date = calc.process_date
    ,target.payment_created_date = calc.payment_created_date_new
    ,process_step = 'STEP: 3', notes = 'Applied payment processing for batch - '+ cast(@current_batch_id as varchar(100))
    output calc.transaction_id
    into payment_processing_log(transaction_id)
from policy_transaction_main target
JOIN (
    Select CASE WHEN balance_adjustment < 0.00 then row_number() over (partition by contract_id,system_id order by calculated_outstanding asc, calculated_commission asc)
        else row_number() over (partition by contract_id,system_id order by calculated_outstanding desc, calculated_commission asc) end as row_rank,*
    from (
        select pb.premium_amount as payment_amount
            ,CASE WHEN pm.total_premium = 0 
                THEN CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3))
                ELSE CASE WHEN cast(((CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3)) / pm.total_premium) * pb.premium_amount) as numeric(18,2)) > CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3)) AND CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3)) > 0
                    THEN CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3))
                    ELSE cast(((CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3)) / pm.total_premium) * pb.premium_amount) as numeric(18,2))
                END
            END as calculated_commission
            
            ,CASE WHEN pm.total_premium = 0 
                THEN CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3))
                ELSE CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3)) - cast(((CAST(CASE WHEN isnull(pm.outstanding_premium,0) <> 0 THEN pm.outstanding_premium ELSE pm.premium_change END AS numeric(18,3)) / pm.total_premium) * pb.premium_amount) as numeric(18,2))
            END as calculated_outstanding
            
            ,pm.total_premium as total_amount
            ,isnull(pb.has_payment,0) as has_payment
            ,balance_adjustment = pb.premium_amount - case when pm.total_premium = 0 then 0 
                else sum(cast(((isnull(pm.outstanding_premium,0) / pm.total_premium) * pb.premium_amount) as numeric(18,2))) over (partition by pm.contract_id,pm.system_id) end
            ,pm.*
            ,pb.process_date
            ,pb.transaction_id
            ,pb.original_transaction_id
            ,pb.created_date as payment_created_date_new
        FROM (
            select contract_id,premium_amount,1 as has_payment,process_date,transaction_id,created_date,system_id,original_transaction_id,premium_type
            from payment_batch_temp with (nolock)
            where batch_num = @min_batch
            and processed_status = 0
        )pb
        JOIN (select SUM(CAST(CASE WHEN isnull(mt.outstanding_premium,0) <> 0 THEN mt.outstanding_premium ELSE mt.premium_change END AS numeric(18,3))) over(partition by mt.contract_id,mt.system_id) as total_premium, mt.*
        from (
            select *, dense_rank() over(partition by a.contract_id,a.system_id order by a.effective_date,a.expiration_date asc,contract_year,transaction_ref_id) as date_rank
            from policy_transaction_main a with (nolock)
            where batch_id = @current_batch_id and processed_status IN ('N','2')
        )mt
        join payment_batch_temp pi with (nolock) on mt.contract_id = pi.contract_id and mt.system_id = pi.system_id
        and (isnull(mt.premium_type,'STANDARD')) = isnull(pi.premium_type,'STANDARD')
        where mt.batch_id = @current_batch_id
        and CAST(CASE WHEN isnull(mt.outstanding_premium,0) <> 0 THEN mt.outstanding_premium ELSE mt.premium_change END AS numeric(18,3)) <> 0
        and mt.record_type in(1,4)
        AND mt.processed_status IN ('N','2')
        and pi.batch_num = @min_batch
        and (isnull(pi.agent_id,0) = 0 or isnull(pi.agent_id,0) = mt.agent_id)
        and date_rank = 1
        ) pm
        ON pb.contract_id = pm.contract_id
        and pb.system_id = pm.system_id
        and (isnull(pb.premium_type,'STANDARD')) = isnull(pm.premium_type,'STANDARD')
        WHERE pm.batch_id = @current_batch_id
        and pm.total_premium <> 0
        and pm.record_type in(1,4)
        AND pm.processed_status IN ('N','2')
    )calculation_data
)calc
ON target.record_id = calc.record_id

update p set processed_status = 1
from payment_batch_temp p 
join payment_processing_log pl with (nolock)
on pl.transaction_id = p.transaction_id

SET @min_batch += 1
END
2

wow, thats a huge bottleneck! try merge statements - they can handle complex upsert logic in one shot. can you break it into smaller chunks? maybe process 10k policies at a time instead of all 300k? what kind of hardware are u running?

3

The main problem is you’re doing iterative batch processing instead of one set-based operation. I’ve dealt with similar performance nightmares and found staging with temp tables and smart indexing works great. First, build a calculation table that mirrors your loop logic but handles all batches at once. Use window functions like SUM() OVER() and DENSE_RANK() to get the running totals and rankings your loop does. The trick is pre-calculating all balance adjustments and commission allocations in one shot before any updates happen. For updates, do them as separate set-based operations - don’t try cramming everything together. Update your policy_transaction_main table first, then bulk insert into payment_processing_log using OUTPUT clauses to grab affected records. Use table variables or temp tables with proper indexing on contract_id and system_id to keep referential integrity between operations. Make sure your temp tables have clustered indexes on join columns before processing. This usually cuts similar workloads from hours down to minutes while getting identical results to the original loop.

4

That loop’s a killer! Try window functions with CTEs - use ROW_NUMBER() to rank records, then do set-based updates. Also, swap those correlated subqueries for proper joins. That’s likely what’s tanking your performance, not just the loop.