temp tables in an SP – killing performance | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

temp tables in an SP – killing performance

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’

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
]]>