Perfomance issue.. TO otimize Joins

Question

Perfomance issue.. TO otimize Joins

r.dilliprasad

Valued Member

Points: 71
More actions
July 21, 2013 at 5:48 am

#395174

I was joining nearly 5 inner joins and 2 left outer join Final with group by
Here i am facing perfomance issue..Please help me!!!!!
select ....
FROM dbo.FactFDDCellQHourlyCalcsEricsson FDDCALC WITH(NOLOCK)
INNER JOIN dbo.FactFDDCellQHourlyPegEricsson1 FDDPEG WITH(NOLOCK)
ON FDDCALC.DimDateID = FDDPEG.DimDateID
AND FDDCALC.DimQHourID = FDDPEG.DimQHourID
AND FDDCALC.DimFDDCellID = FDDPEG.DimFDDCellID
INNER JOIN dbo.FactFDDCellQHourlyPegEricsson2 FDDPEG2 WITH(NOLOCK)
ON FDDCALC.DimDateID = FDDPEG2.DimDateID
AND FDDCALC.DimQHourID = FDDPEG2.DimQHourID
AND FDDCALC.DimFDDCellID = FDDPEG2.DimFDDCellID
INNER JOIN DimQHour DQ
ON FDDCALC.DimQHourID = DQ.DimQHourKey
LEFT OUTER JOIN #FactFreqCellRelationQHourlyCalcsEricsson CALC
ON FDDCALC.DimDateID = CALC.DimDateID
AND FDDCALC.DimQHourID = CALC.DimQHourID
AND FDDCALC.OSSC_RC = CALC.OSSC_RC
AND FDDCALC.MeContext = CALC.MeContext
AND FDDCALC.EUtranCellFDD = CALC.EUtranCellFDD
LEFT OUTER JOIN dbo.View_DimFDDCell_Mkt99 D99 WITH (NOLOCK)
ON FDDCALC.DimFDDCellID = D99.DimFDDCellID
INNER JOIN #TEMP_RANK SPL
ON FDDCALC.DimDateID=SPL.DimDateID
AND FDDCALC.DimQHourID = SPL.DimQHourID
AND FDDCALC.DimFDDCellID = SPL.DimFDDCellID
INNER JOIN @Temp_RFASFDDCell_HOURLY Temp
ON FDDCALC.OSSC_RC = Temp.OSSC_RC
AND FDDCALC.MeContext = Temp.eNodeB
WHERE FDDCALC.DimDateID = @DimDateID and DQ.[HOUR]= @DimHourID
GROUP BYFDDCALC.DimFDDCellID

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

To add to what Gail said, you have at least one view involved in this and you need to include the code and the table/index information for that along with the other things recommended in Gail's article.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Oh, and ditch the nolock

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass