optimize query

[kingstonr]

folks,

In my following query I am using Table called vwrampositions which has millions of rows.
In my query after doing the join conditions and finally I am giving the where condition with date.so its doing all the join condition with millions of row and finally comes into where condition and it takes lot of time.

Can any body suggest me how to modify the below query like it filter it with where condition before making the joins.

SELECT rampos.Position_ID,YB_riskcode as YBName,rampos.PosRunID,rampos.AsOfDt,rampos.Portfolio_ID,rampos.Security_ID,rampos.AccountingStatus_ID,
rampos.ParOrShare,rampos.OriginalCostUSD,rampos.MVUSD,rampos.AIUSD,rampos.UGLUSD,rampos.SourceSys_ID,rampos.OriginalFace,
rampos.CurrentFace,sm.SecName, sm.Cusip, sm.isin,sm.PrimarySecID,sm.Coupon,sm.maturity,p.ShortName,
SourceSysName as SourceSystem,ramprice.porpx as Price,ramprice.YBFullPx as YBFullPrice,ramprice.YBPx as CleanPrice
FROM (vwRAMPositions rampos inner join vwsecmaster sm ON rampos.Security_ID = sm.SecID)
inner join Portfolio p ON rampos.Portfolio_ID = p.Portfolio_ID
inner join sourcesystem srcsystem on srcsystem.sourcesys_ID=rampos.sourcesys_ID
inner join vwramprice as ramprice on rampos.security_id=ramprice.secid and ramprice.effectivedate=@AsOfDate
left join alternatecode alc on alc.portfolio_id=rampos.Portfolio_ID
Where rampos.AsOfDt=@AsOfDate
order by ShortName

 

[SQLDenis]

can you show us the execution plan?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[Lamprey13]

I don't know if it'll help, but there are two things to try without know more about your data.

1. Move the condition to the join:

...
FROM 
		vwRAMPositions rampos
inner join 
	vwsecmaster sm 
	ON rampos.Security_ID = sm.SecID
	[b]AND rampos.AsOfDt = @AsOfDate[/b]
...

2. Try using a derived table:

...
FROM 
(
	SELECT
		Position_ID,
		PosRunID,
		AsOfDt,
		Portfolio_ID,
		Security_ID,
		AccountingStatus_ID, 
		ParOrShare,
		OriginalCostUSD,
		MVUSD,
		AIUSD,
		UGLUSD,
		SourceSys_ID,
		OriginalFace,
		CurrentFace
	FROM
		vwRAMPositions
	WHERE
		AsOfDt = @AsOfDate
) rampos
inner join 
	vwsecmaster sm 
	ON rampos.Security_ID = sm.SecID
...

-Ryan