SQL Server Performance Forum – Threads Archive

How can I write this qry better

This query does too many table scans and logical reads.userholdingview is a HUGE HUGE view. Any way I can optimize this query ?
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.INQ_BASIS_NUM = @inq_basis_num
AND D.instr_id = F.instr_id
AND D.As_of_tms <= @OpeningBalanceStartDate)
and A.INQ_BASIS_NUM = @inq_basis_num
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

---

How is the definition for userholdingview? —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de

---

]]>