TSQL Joins Conditions.. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

TSQL Joins Conditions..

Author

Topic

raghav99k
Starting Member

32 Posts

Posted - 2011-11-16 : 02:45:25

INSERT INTO SRTable
(DataSource,
ID,
Typeq
Code,
CodeProc,
CodeSP
IDNtwrk,
IDProv,
LID,
LEID,
CSCode)
SELECT C.DataSource,
M.ID,
Dx.Typeq
SP.Code,
XP.CodeProc,
PSD.CodeSP
PN.IDNtwrk,
RP.IDProv,
EM.LID,
P.LEID,
HG.CSCode
FROM CPTable C
JOIN MDTable M ON C.KeyM=M.KeyM
JOIN ProvDTable SP ON C.SPKey=SP.PSKey
JOIN ProcDTable XP ON C.ProcKey=XP.ProcKey
JOIN DTEDTable SvFDt ON C.ServKey=SvFDt.DTKey
JOIN DTEDTable SvTDt ON C.ServThruKey=SvTDt.DTKey
JOIN DTEDTable RdDt ON C.RecKey=RdDt.DTKey
JOIN DTEDTable PdDt ON C.PDy=PdDt.DTKey
JOIN PSDTable PSD ON C.PSrvKey = PSD.PSrvKey
JOIN DIDTable Dx ON C.PDoKey=Dx.DoKey
JOIN DTEDTable ESvFDt ON C.EarKey=ESvFDt.DTKey
JOIN DTEDTable AdjDt ON C.AdKey=AdjDt.DTKey
JOIN CCDTable CC ON C.CLSKey=CC.CLSKey
JOIN PrDTable P ON C.ClPtKey=P.ClPtKey
JOIN SCDTable HG ON C.SSCkey=HG.SSCkey
JOIN SDTable S ON C.SitKey=S.SitKey
JOIN EMDTable EM ON C.MEKey=EM.MEKey
JOIN ProvDTable RP ON C.RPKey=RP.PSKey
JOIN NPDTable PN ON C.PNtwrk=PN.PNtwrk

in the above query, with the use of more joins the query exection time is much time(around 10hrs-12hrs) to insert 10 Crore of records..

if u see the DTETable is using 6 times with Comparing the DTkey with diff colums of same table(CPTable C)..Can we rewrite this in single query by decreasing the join conditions which helps in improve the performance.

in the same way ProvDTable too..

Can we rewrite the same query without joins r with joins in more optimised way..any suggestions pls..

Thnks in Advance..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 03:38:52

you can . use UNPIVOT for that
http://msdn.microsoft.com/en-us/library/ms177410.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghav99k
Starting Member

32 Posts

Posted - 2011-11-16 : 04:17:07

Hi Visakh..

Thanks for ur sugestion. but using pivot on join condition...?Can u pls explain it bit clear..Thanks..

raghav99k
Starting Member

32 Posts

Posted - 2011-11-16 : 04:19:13

we are using same column right..on same table. But pivot wil help in rows into colums..How it can be used here.?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-16 : 05:04:08

quote:
Originally posted by raghav99k

we are using same column right..on same table. But pivot wil help in rows into colums..How it can be used here.?

i was suggesting UNPIVOT not PIVOT

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

raghav99k
Starting Member

32 Posts

Posted - 2011-11-16 : 05:21:47

ys u ur corect.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources