这条sql执行效率很低，执行一次需要30多秒，我已经建立了索引和一些简单的优化，请大家帮忙看看有没有什么地方还可以优化的

select count(1) as salTarHosNum,regionId from
(
SELECT
Hospital.ID,region.ID as regionId
FROM
WT_S_Hospital AS Hospital
LEFT JOIN
(SELECT DISTINCT HpOrDsId,YEAR,MONTH,brandID,product.ProductCategory_ID
FROM
WT_S_Hospital_MR AS hospitalMr
LEFT JOIN
WT_S_StandardProduct AS product
ON
hospitalMr.brandID = product.ID
WHERE
YEAR = 2012and MONTH = 10) as hospitalMr
ON
hospitalMr.HpOrDsId = Hospital.ID
LEFT JOIN
(
SELECT
CityId,region.ID,region.RegionName,region.ProductGroupID
FROM
WT_S_Region_Province AS regionProvince
LEFT JOIN
WT_S_Region AS region
ON
regionProvince.SalesAreaid = region.ID
WHERE
regionProvince.IsValid = 1
AND
regionProvince.year = region.Year
AND
regionProvince.year= 2012 ) as region ON
Hospital.City = region.CityId
AND
hospitalMr.ProductCategory_ID = region.ProductGroupID
WHERE brandID in (1,2,3,4,5,6,7,9,10,11,12,13,14,15)
and region.id in(16,17,18,19,20,21,22,23,24,25,26,27,58,59,60,61,62,63,64,65,66)
and isnull(IS_TARGET_HOSPITAL,'') = 'true' ) as hospital
where exists(select 1 from view_WT_S_SalesCompletion as salesdata where salesdata.hosipitalId=hospital.ID
and ((salesdata.year = 2012 AND salesdata.month = 4) OR (salesdata.year = 2012 AND salesdata.month = 5)
OR (salesdata.year = 2012 AND salesdata.month = 6) OR (salesdata.year = 2012 AND salesdata.month = 7)
OR (salesdata.year = 2012 AND salesdata.month = 8) OR (salesdata.year = 2012 AND salesdata.month = 9)
OR (salesdata.year = 2012 AND salesdata.month = 10)) )
group by hospital.regionId having hospital.regionId is not null
[解决办法]
你的开销主要在排序，看了一下应该是distinct引起的，看看你的where条件如何改进来取消distinct操作。减少排序的开销
[解决办法]
人的，不一定能化多少。
1、brandID in (1,2,3,4,5,6,7,9,10,11,12,13,14,15) 可以放到SELECT DISTINCT那句面，然後前面的不用Left join，而用inner join。
2、region.id in(16,17,18,19,20,21,22,23,24,25,26,27,58,59,60,61,62,63,64,65,66) 放在(...)AS region ON那句面，然後前面的也不用Left join，而用inner join。
3、IS_TARGET_HOSPITAL那不用isnull吧，直接等於就行了。列是哪表中的，直接表where。
4、最后的having好像也有吧，直接用where就可以了，什麽要用having？？？
5、如果某表中的列比多，可以改一下表的接。
如：
LEFT JOIN
WT_S_StandardProduct AS product
ON
hospitalMr.brandID = product.ID
改成
LEFT JOIN
(select ProductCategory_ID, id from WT_S_StandardProduct) AS product
ON
hospitalMr.brandID = product.ID
最后一招，SQL Server自索引化功能，跑一遍，自提示你需要加什麽索引的。