SQL Server Performance

Read and Update Same Table - PAGEIOLATCH_SH

Discussion in 'General DBA Questions' started by alimmia, Jan 25, 2007.

  1. alimmia New Member

    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
  2. Luis Martin Moderator

    Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.

    Did you see execution plan?.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. alimmia New Member

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

    Thanks,


    Alim Mia
  4. satya Moderator

    Why don't you use WITH NOLOCK hint.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  5. mmarovic Active Member

    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.

Share This Page