Read and Update Same Table - PAGEIOLATCH_SH

I am trying to update a table column after reading the same table through a function. However, it is taking too long. I am getting waiting type: PAGEIOLATCH_ SH.

-------- Update ----
update dbo. TESTU2_Cube_temp
set invoice_count = dbo.fnTESTU2(invoice_num, transaction_id)
--------
-------- Function -----
Create function dbo.fnTestu2(@inv int,@chg_txn int)
returns int
as
begin
declare @inv_ct int;
select @inv_ct = case when (select min(transaction_id) from dbo. TESTU2_Cube_temp
where invoice_num = @inv ) = @chg_txn then 1 else 0 end

return @inv_ct
end
---------

Any hints will be appreciated.
Thanks,


Alim Mia

Yes. The function is useing proper indexes. Am I updating rightly?

Thanks,


Alim Mia

I would use default 0 (zero) on invoice_count during insert and then next code:


update a
set a.invoice_count = 1
from (select invoice_num, min(transaction_id) as transaction_id
from dbo. TESTU2_Cube_temp
group by invoice_num)as b
inner loop join dbo.TESTU2_Cube_tempas a
on a.invoice_num = b.invoice_num and
a.transaction_id = b.transaction_id
option (force order)

If transaction_id is pk, then it is enough to join just on transaction_id column, otherwise index on both invoice_num and transaction_id in that column order would help performance.