SQL Server Performance

How can I write this qry better

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

  1. nbhawnani New Member

    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
  2. FrankKalis Moderator

Share This Page