SQL Server Performance Forum – Threads Archive

TempTable Vs Derived Tables

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

---

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

---

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.

---

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

---

]]>