Help to increase query performance.

[kingstonr]

HI,
I am using simple query thats two views and one table are
referred in the table.In one view I am having multiple rows so I am using distinct.my query almost takes 8mts to complete.
Below is my query.Pl somebody help me to modify the query
for efficiency.I am using sql2005

SELECT vsm.secid,vsm.PrimarySecId,vsm.PrimarySecType,vsm.SecName,vsm.LongDesc,vsm.Coupon,
vsm.Maturity, vsm.SecClass,vsm.PerfClass,vsm.SecType, vsm.Moody, vsm.SP,vsm.CountryofIssue,
vsm.Country, vsm.Currency,sp.Price,
case
When vsm.MgrName is Null or rtrim(vsm.MgrName) = '' THEN
'UNASSIGNED'
ELSE
vsm.MgrName
End MgrName
from vwsecmaster vsm
inner join
(Select distinct security_id,AsOfDt from vwPositions where AsOfDt ='2007-01-23')pos
on Pos.Security_id = vsm.SecID
inner join
(select secid,price from secprice where pricedate='2007-01-23')sp
on sp.secid = vsm.secid
--and sp.PriceDate = '2007-01-23' and
where vsm.PerfClass = 'DEBT'

 

[gmmastros]

Have you tried displaying the execution plan?

In Management studio, press CTRL-M (CTRL-K For Query Analyzer) and then run this query. After the query is done running, take a look at the execution plan (there will be a tab for this).

Check to see if there are any table scans. If there are, then you can speed up the query by adding indexes to the table.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kingstonr]

George,

Thx.Is there a way that I can tune the qry in the first case.especially the way I am joining with distinct query.

 

[gmmastros]

Possibly.

However, if indexes are not being used, then creating the appropriate indexes will do more to speed up the query.

If you see table scans in the execution plan, then you know indexes are not being used.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Since you are using views, it is especially important to be using indexed views or the problem could be the view itself.

Questions about posting. See faq183-874