SQL Server Performance

temp tables in an SP - killing performance

Discussion in 'T-SQL Performance Tuning for Developers' started by nbhawnani, Jun 18, 2007.

  1. nbhawnani New Member

    Hi :

    I have an SP whose performance is painfully slow. Below are some queries which i suspect to be the cause.
    1 temp table updating another ...
    Any help will be appreciated.

    -- creating temp table --
    CREATE TABLE #Positions (ACCT_ID CHAR(12), BK_ID CHAR(4), ORG_ID CHAR(4), As_of_tms datetime, ISS_FLD2_DESC varchar(40), LastClsBal float )
    CREATE CLUSTERED INDEX Positions_idx1 on #Positions(ACCT_ID , BK_ID , ORG_ID)
    CREATE NONCLUSTERED INDEX Positions_idx2 on #Positions(ISS_FLD2_DESC,As_of_tms)

    -- inserting data into a temp table --
    INSERT INTO #POSITIONS -- computing opening balance total for security
    SELECT B.Acct_id,B.bk_id, B.org_ID,A.AS_of_tms, C.ISS_FLD2_DESC, sum(isnull(A.fld1_amt, 0))
    FROM userholdingview A (nolock) INNER JOIN #ACCOUNTS B (nolock)
    ON A.ACCT_ID = B.ACCT_ID AND A.BK_ID = B.BK_ID AND A.ORG_ID = B.ORG_ID
    INNER JOIN #InstrTmp C
    ON A.instr_id = C.instr_id
    WHERE A.As_of_tms = (select max(D.As_of_tms )
    FROM userholdingview D (nolock), #ACCOUNTS E ,#InstrTmp F (nolock)
    WHERE D.ACCT_ID =E.ACCT_ID AND D.BK_ID = E.BK_ID AND D.ORG_ID = E.ORG_ID
    AND A.ACCT_ID =D.ACCT_ID AND D.BK_ID = A.BK_ID AND D.ORG_ID = A.ORG_ID
    AND D.instr_id = F.instr_id
    AND D.As_of_tms <= @OpeningBalanceStartDate)
    GROUP BY B.Acct_id,B.bk_id, B.org_ID,C.ISS_FLD2_DESC,A.AS_of_tms
    ORDER BY B.Acct_id,B.bk_id, B.org_ID,C.ISS_FLD2_DESC,A.AS_of_tms


    -- updating another temp table --

    update #tmp_tranevent
    set acct_id = A.ACCT_ID,
    acct_nme = A.ACCT_NME,
    bk_id = D.BK_ID,
    org_id = D.ORG_ID,
    trn_desc = 'Opening Balance for '+ convert(varchar(11),@ReportStartDate,106),
    QUANTITY = P.LastClsBal
    from UserHoldingView D INNER JOIN #ACCOUNTS A
    ON D.ACCT_ID = A.ACCT_ID and D.BK_ID = A.BK_ID and D.ORG_ID = A.ORG_ID
    Inner JOIN #Positions P
    on D.ACCT_ID = P.ACCT_ID and D.BK_ID = P.BK_ID and D.ORG_ID = P.ORG_ID and D.AS_OF_TMS = P.As_of_tms and D.ISS_FLD2_DESC = P.ISS_FLD2_DESC
    where D.INQ_BASIS_NUM = @inq_basis_num
    and D.ACCT_ID = [#tmp_tranevent].ACCT_ID and D.BK_ID = [#tmp_tranevent].BK_ID and D.ORG_ID = [#tmp_tranevent].ORG_ID
    and D.ISS_FLD2_DESC = [#tmp_tranevent].ISS_FLD2_DESC and [#tmp_tranevent].rcd_typ = '00' and [#tmp_tranevent].rcd_num = -99 and [#tmp_tranevent].reg_nme = 'TOTAL FOR SECURITY'
  2. joechang New Member

    run Profiler to see if this proc recompiles (ie Stored Procedures->SP:Recompile)

    if so, look into the use of OPTION (KEEP PLAN)

    also, run the sp from QA with the SHOW Execution plan option
    also learn to use SET STATISTICS TIME ON, and IO too

Share This Page