SQL Server Performance

TempTable Vs Derived Tables

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by anilsaritha, Nov 8, 2006.

  1. anilsaritha New Member

    Dear Gurus

    Temp tables increases the performance or derived tables increases the performance?

    Here below i am posting a query which i had created using derived tables.Do you feel replacing the derived tables with temp tables increases the performance?

    Kindly clarify.

    The query is a bit lengthy one,sorry for posting such a lengthy query

    Select LONINSTENT.Brid,LONINSTENT.Prdid,LONINSTENT.AcctId,
    LONINSTENT.MemLName,LONINSTENT.OpeningDt as FirstDisbDate,
    LONINSTLDGRHDR.BusnDt as LAstCollectionDate,
    LONINSTENT.ExpiryDt,LONIRDETL.EffDate as IntSlabDate,
    LONIRDETL.ROI as InterestRate, LONACCTBALN.Principal,
    LONACCTBALN.Interest, LONACCTBALN.Penalty,
    LONACCTBALN.PenalInterest,LONACCTBALN.Others
    From
    (SelectBrid,Prdid,Acctid,CusId,MemLName,OpeningDt,
    AccStatus,ExpiryDt,Intvariance
    FromLON_TINSTENT
    WhereBrid='8'
    AndAccStatus<>'C')LONINSTENT

    Left outer Join

    (SelectT1.Brid,T1.Prdid,T1.Acctid,T1.EffDate,T1.EMIAMT
    FromLON_tAcctSchDetl T1,
    Lon_tInstEnt IE
    WhereT1.Brid='8'
    AndT1.Brid=IE.Brid
    AndT1.Prdid=IE.prdid
    AndT1.AcctId=IE.AcctId
    AndIE.AccStatus<>'C'
    AndT1.Pk_Id In (Select Top 1 Pk_Id
    FromLON_tAcctSchDetl T2
    Where T1.BrId=T2.BrId
    AndT1.PrdId=T2.Prdid
    AndT1.AcctId=T2.AcctId
    AndT2.Purpose='DISB'
    Order By Pk_Id Desc)) LONSCHDETL on LONINSTENT.Brid =LONSCHDETL.Brid
    AndLONINSTENT.Prdid=LONSCHDETL.Prdid
    AndLONINSTENT.Acctid=LONSCHDETL.Acctid

    Right outer join

    (Select Brid,Prdid,Acctid,BusnDt
    FromLON_tInstLdgrHdr T3
    WhereBrid='8'
    AndPk_Id in (Select Top 1 T4.Pk_Id
    FromLON_tInstLdgrHdr T4,
    LON_TINSTENTT5
    WhereT4.Brid=T5.Brid
    AndT4.Prdid=T5.Prdid
    AndT4.AcctId=T5.AcctId
    AndT5.AccStatus<>'C'
    AndT3.Brid=T4.Brid
    AndT3.Prdid=T4.Prdid
    AndT3.AcctId=T4.AcctId
    And T3.Brid=T5.Brid
    AndT3.Prdid=T5.Prdid
    AndT3.AcctId=T5.AcctId
    Order By BusnDt Desc, T4.Pk_Id Desc)) as LONINSTLDGRHDR
    On LONINSTENT.Brid =LONINSTLDGRHDR.Brid
    AndLONINSTENT.Prdid=LONINSTLDGRHDR.Prdid
    AndLONINSTENT.Acctid=LONINSTLDGRHDR.Acctid

    Inner Join

    (Selecta.Brid,a.Prdid,a.Acctid,a.EffDate,b.IRID, b.ROI
    FromLon_TAcctIrHdr a,Lon_Tacctirdetl b, Lon_tInstEnt IE
    Wherea.Brid='8'
    and a.prdid=ie.prdid
    anda.acctid=ie.acctid
    and a.brid = ie.brid
    and ie.accstatus <> 'c'
    and (ie.HasIntDepdn = 1 or HasFixedIntRt = 'Y')
    Anda.irid=b.irid
    Anda.IRID in (Select top 1 c.IRID
    FromLon_tacctirhdr c
    Wherea.prdid=c.prdid
    anda.acctid=c.acctid
    and a.brid = c.brid
    and c.brid='8'
    and a.IRID = b.IRID
    Order by c.EffDate Desc
    ))as LONIRDETL on
    LONIRDETL.Brid =LONINSTENT.Brid
    AndLONIRDETL.Prdid=LONINSTENT.Prdid
    AndLONIRDETL.Acctid=LONINSTENT.Acctid

    Inner Join

    (SELECT AB.BrID, AB.PrdID, AB.AcctID,
    IsNull(SUM(CASE WHEN OptnTypeID = '1' THEN AmtDueFrm End),0) AS 'Principal',
    IsNull(SUM(CASE WHEN OptnTypeID ='2' THEN AmtDueFrm End),0) AS 'Interest',
    IsNull(SUM(CASE WHEN OptnTypeID ='8' THEN AmtDueFrm End),0) AS 'Penalty',
    IsNull(SUM(CASE WHEN OptnTypeID ='6' THEN AmtDueFrm End),0) AS 'PenalInterest',
    IsNull(SUM(CASE WHEN OptnTypeID Not IN ('1','2','6','8') THEN AmtDueFrm End),0) AS 'Others'
    FROM LON_tAcctBalnDetl ABD, LON_tAcctBaln AB
    where AB.PK_ID = ABD.BalnHdr_FK
    And BrID = 8
    And AB.TotOS > 0
    --And PrdID = 20
    Group by AB.BrID, AB.PrdID, AB.AcctID) As LONACCTBALN
    on LONINSTENT.Brid =LONACCTBALN.Brid
    AndLONINSTENT.Prdid=LONACCTBALN.Prdid
    AndLONINSTENT.Acctid=LONACCTBALN.Acctid
    Order by LONINSTENT.BrID, Cast(LONINSTENT.PrdID as Integer), Cast(LONINSTENT.AcctID As Integer)






    Deep Regards
    Anil

    Never Give Up
  2. Roji. P. Thomas New Member

    quote:Originally posted by anilsaritha

    Dear Gurus
    Temp tables increases the performance or derived tables increases the performance?
    It depends. With derived tables, Query Optimizer is free to promote the derived table to a normal join with the underlying table.

    Sometime materializeing the derived table into a temprary table and adding additional indexes may help, sometimes not.


    Roji. P. Thomas
    http://toponewithties.blogspot.com
  3. Adriaan New Member

    Temp tables mean you have to copy data into them, meaning extra execution time. If the amount of data is relatively large, you also need indexes on temp tables, which again take time to populate.

    As long as your indexes are appropriate, derived tables should provide sufficient performance.

    ... this said without even looking at your query in detail, which does include a couple of red flag items.
  4. anilsaritha New Member

    Dear Roji And Adriaan

    Thanks a lot for your responses.
    The tables used in my query are indexed and i feel like continuing with the derived table itself

    Deep Regards
    Anil Kottayam





    Never Give Up

Share This Page