optimize a stored procedure

Hi ,
I have this complex stored procedure, that I was happy with but unfortunately the perfoermance fo this SP are very poor.
Do you have any idea of how I can optimize it?
Thanks
Wattier
SELECT DISTINCT dateend,active,Tparts.idpart,TParts.Namepart,
COALESCE( (SELECT SUM(nb) FROM TEChart WHERE TEChart.part = TParts.idpart and qc=0 and DateInsp>=@.start and DateInsp<=@.end),0) AS review
,COALESCE((SELECT SUM(nb)
FROM TEChart
WHERE TEChart.part = TParts.idpart and TEchart.qc = @.concern and DateInsp>=@.start andDateInsp<=@.end),0) AS reject,
COALESCE
((SELECT - DATEDIFF(day, GETDATE(), MAX(DateInsp))
FROM TEchart
WHERE TEchart.QC <> 0 AND TEchart.part = TParts.idPart
GROUP BY TEchart.part),
(SELECT - DATEDIFF(day, GETDATE(),
(SELECT datestart
FROM Tproject
WHERE idproject= @.project)))) AS jour
FROM Tparts,TPartQC
WHERETparts.project=@.project andTPartQC.qc=@.concern and TPartQC.part=Tparts.idpart
order by reject DESC,active DESC,review DESC
Let me preface any discussion of optimization by saying measure, changea small bit, measure again. Did it improve or not? If so,keep the change. If not, try something else. And sometimesyou have to try combinations of things.
Do you have indexes on the right columns, including a clustered indexon the more heavily used column for searches? Are your indexesdefragmented and are your statistics up-to-date? Have you run theIndex Tuning Wizard and tried some or all of its recommendations? Have you looked at the query's execution plan and made sure there areno unnecessary table scans?
But off-hand, I'd look at trying to get rid of the DISTINCT operationand the various COALESCE operations. Those, in general, cause aperformance (all other things being equal).
Then check the following sites:
http://www.sql-server-performance.com/transact_sql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_536v.asp
http://www.sql-server-performance.com/index_tuning_wizard_tips.asp

|||Hi ,
Thanks for the precious information you gave me. I have been checking all the index, running the tuning wizard, but my procedure is still very slow.
I would like to find a way to get rid of the coalesce and the subquery but I can not find the solution.
Thanks