select 语句优化！

Question

select T1.ReasonID as int_reasonid,T2.ReasonName as nvar_reason,
count(TyreID) as RENUM,
(select count(TYRNO) from CTVDI where TYRNO in (select TyreID from tb_TE_RecipeTrackInfo 
where ReasonID=T1.ReasonID  and RecordTime between @strStart and @strEnd) and ATYPE='01') as CTNUM,
cast(((select cast(isnull(count(TYRNO),1) as decimal(18,4))  from CTVDI where TYRNO in (select TyreID from tb_TE_RecipeTrackInfo 
where ReasonID=T1.ReasonID and RecordTime between @strStart and @strEnd) and ATYPE='01')/(case isnull(count(TyreID),1) when 0 then 1 else isnull(count(TyreID),1) end ))*100 as decimal(18,2)) AS RERAT
from tb_TE_RecipeTrackInfo T1 join tb_TE_RecipeTrackReason T2 ON T1.ReasonID=T2.ReasonID
where RecordTime between @strStart and @strEnd 
group by ReasonID
存储过程中涉及的一个查询语句，性能比较低，希望优化，但是不知道如何下手，目的，把嵌套子查询修改为相关子查询，提高性能的其他优化更好。

Answer 1

SELECT   t1.reasonid AS int_reasonid,
         t2.reasonname AS nvar_reason,
         Count(tyreid) AS renum,
         (SELECT Count(tyrno)
          FROM   ctvdi
          WHERE  tyrno IN (SELECT tyreid
                           FROM   tb_te_recipetrackinfo
                           WHERE  reasonid = t1.reasonid
                                  AND recordtime BETWEEN @strStart
                                                         AND @strEnd)
                 AND atype = '01') AS ctnum,
         Cast(((SELECT Cast(Isnull(Count(tyrno),1) AS DECIMAL(18,4))
                FROM   ctvdi
                WHERE  tyrno IN (SELECT tyreid
                                 FROM   tb_te_recipetrackinfo
                                 WHERE  reasonid = t1.reasonid
                                        AND recordtime BETWEEN @strStart
                                                               AND @strEnd)
                       AND atype = '01') / (CASE Isnull(Count(tyreid),1) 
                                              WHEN 0 THEN 1
                                              ELSE Isnull(Count(tyreid),1)
                                            END)) * 100 AS DECIMAL(18,2)) AS rerat
FROM     tb_te_recipetrackinfo t1
         JOIN tb_te_recipetrackreason t2
           ON t1.reasonid = t2.reasonid
WHERE    recordtime BETWEEN @strStart
                            AND @strEnd
GROUP BY reasonid这样看的清楚点 具体优化 你一看就知道了

Answer 2

--TRYSELECT   T1.REASONID AS INT_REASONID,
         T2.REASONNAME AS NVAR_REASON,
         COUNT(TYREID) AS RENUM,
         (SELECT COUNT(TYRNO)
          FROM   CTVDI A,
                 TB_TE_RECIPETRACKINFO B
          WHERE  A.TYRNO = B.TYREID
                 AND B.REASONID = T1.REASONID
                 AND B.RECORDTIME BETWEEN @strStart
                                          AND @strEnd
                 AND A.ATYPE = '01') AS CTNUM,
         CAST(((SELECT CAST(ISNULL(COUNT(TYRNO),1) AS DECIMAL(18,4))
                FROM   CTVDI A,
                       TB_TE_RECIPETRACKINFO B
                WHERE  A.TYRNO = B.TYREID
                       AND B.REASONID = T1.REASONID
                       AND B.RECORDTIME BETWEEN @strStart
                                                AND @strEnd
                       AND A.ATYPE = '01') / (CASE ISNULL(COUNT(TYREID),1) 
                                                WHEN 0 THEN 1
                                                ELSE ISNULL(COUNT(TYREID),1)
                                              END)) * 100 AS DECIMAL(18,2)) AS RERAT
FROM     TB_TE_RECIPETRACKINFO T1
         JOIN TB_TE_RECIPETRACKREASON T2
           ON T1.REASONID = T2.REASONID
WHERE    RECORDTIME BETWEEN @strStart
                            AND @strEnd
GROUP BY T1.REASONID,
         T2.REASONNAME
         --注意建索引