一个SQL查询优化

维护一个系统，近日一个页面打开时老出错，提示超时。

后台c#代码如下：

string sql="";
string sqlMain="";

DataTable dt=DbOperate.ExecuteDataset("select DataCode,DataName from S_DataCode where FieldName='CourseTypeCode'").Tables[0];


for(int i=0;i<dt.Rows.Count;i++)
{
    if(sql.Equals(""))
        {
           sql+="d"+dt.Rows[i]["DataCode"].ToString()+"."+dt.Rows[i]["DataName"].ToString()+"学分";
        }
        else
        {
           sql+=", d"+dt.Rows[i]["DataCode"].ToString()+"."+dt.Rows[i]["DataName"].ToString()+"学分";
        }
        sqlMain+=" Left outer join (Select studentNo ,Sum(Credit) as "+dt.Rows[i]["DataName"].ToString()+"学分  From vscoScoreLast where {0} and CourseTypeCode='"+dt.Rows[i]["DataCode"].ToString()+"' group by studentNo ) as d"+dt.Rows[i]["DataCode"].ToString()+"   on a.StudentNo=d"+dt.Rows[i]["DataCode"].ToString()+".StudentNo";               
}
       sqlResult="select a.*,"+sql+" from (Select 0 as ID,GradeSubjectNo,GradeSubjectName,ClassNo,ClassName,StudentNo,Name,Sum(isnull(Credit,0)) as 总学分,Sum(isnull(Credit,0) * isnull(ScoreDot,0) ) as  总学分绩点,( Sum(isnull(Credit,0)*isnull(ScoreDot,0))/( case   Sum(IsNull(Credit,0)) when  0 then 1 else  Sum(IsNull(Credit,0)) end )) as 平均学分绩点,Count(*) as 所修门数 from vscoScoreLast where {0} group by StudentNo,Name,ClassNo,ClassName,GradeSubjectNo,GradeSubjectName) as a "+sqlMain;   

sqlResult=string.Format(sqlResult,this.WhereClause);    

调试发现生成的sqlResult语句如下，放到查询分析器一查询花费了90s左右：

select a.*,d1.必修课学分, d10.任选课学分, d2.专业限选课学分, d3.公共任选课学分, d4.专业任选课学分, d5.公共必修课学分,
d6.专业必修课学分, d7.限定选修课学分, d8.任意选修课学分, d9.毕业实习学分 from
(
Select 0 as ID,GradeSubjectNo,GradeSubjectName,ClassNo,ClassName,StudentNo,Name,Sum(isnull(Credit,0)) as 总学分,
Sum(isnull(Credit,0) * isnull(ScoreDot,0) ) as 总学分绩点,
( Sum(isnull(Credit,0)*isnull(ScoreDot,0))/( case Sum(IsNull(Credit,0)) when 0 then 1 else Sum(IsNull(Credit,0)) end )) as 平均学分绩点,
Count(*) as 所修门数 from vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641'
and CourseTypeCode in ( Select CourseTypeCode From couType)
group by StudentNo,Name,ClassNo,ClassName,GradeSubjectNo,GradeSubjectName
) as a
Left outer join
(Select studentNo ,Sum(Credit) as 必修课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='1' group by studentNo ) as d1
on a.StudentNo=d1.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 任选课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='10' group by studentNo ) as d10
on a.StudentNo=d10.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 专业限选课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='2' group by studentNo ) as d2
on a.StudentNo=d2.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 公共任选课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='3' group by studentNo ) as d3
on a.StudentNo=d3.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 专业任选课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='4' group by studentNo ) as d4
on a.StudentNo=d4.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 公共必修课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='5' group by studentNo ) as d5
on a.StudentNo=d5.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 专业必修课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='6' group by studentNo ) as d6
on a.StudentNo=d6.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 限定选修课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='7' group by studentNo ) as d7
on a.StudentNo=d7.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 任意选修课学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='8' group by studentNo ) as d8
on a.StudentNo=d8.StudentNo
Left outer join
(Select studentNo ,Sum(Credit) as 毕业实习学分 From vscoScoreLast where TermNo = '2009-201001' and Grade='2008' and GradeSubjectNo='200830641' and CourseTypeCode in ( Select CourseTypeCode From couType) and CourseTypeCode='9' group by studentNo ) as d9
on a.StudentNo=d9.StudentNo

一个简单的东西写得这么复杂...，把它修改了一番，执行查询结果只几秒：

Select 0 as ID,GradeSubjectNo,GradeSubjectName,ClassNo,ClassName,StudentNo,Name,Sum(isnull(Credit,0)) as 总学分,
Sum(isnull(Credit,0) * isnull(ScoreDot,0) ) as 总学分绩点,
( Sum(isnull(Credit,0)*isnull(ScoreDot,0))/( case Sum(IsNull(Credit,0)) when 0 then 1 else Sum(IsNull(Credit,0)) end )) as 平均学分绩点,
Count(*) as 所修门数,
sum(case CourseTypeCode when '1' then Credit  end) as 必修课学分,
sum(case CourseTypeCode when '10' then Credit end) as 任选课学分,
sum(case CourseTypeCode when '2' then Credit  end) as 专业限选课学分,
sum(case CourseTypeCode when '3' then Credit  end) as 公共任选课学分,
sum(case CourseTypeCode when '4' then Credit  end) as 专业任选课学分,
sum(case CourseTypeCode when '5' then Credit  end) as 公共必修课学分,
sum(case CourseTypeCode when '6' then Credit  end) as 专业必修课学分,
sum(case CourseTypeCode when '7' then Credit  end) as 限定选修课学分,
sum(case CourseTypeCode when '8' then Credit  end) as 任意选修课学分,
sum(case CourseTypeCode when '9' then Credit  end) as 毕业实习学分
from vscoScoreLast
where TermNo = '2009-201002' and Grade='2008' and GradeSubjectNo='200830641'
and CourseTypeCode in ( Select CourseTypeCode From couType)
group by StudentNo,Name,ClassNo,ClassName,GradeSubjectNo,GradeSubjectName

由此修改页面的C#代码如下：

string result = "";
StringBuilder sb = new StringBuilder(); 
for(int i=0;i<dt.Rows.Count;i++)			
{				
     sb.AppendFormat("sum(case CourseTypeCode when '{0}' then Credit end) as {1}学分,",dt.Rows[i]["DataCode"].ToString(),dt.Rows[i]["DataName"].ToString());
}
if(sb.ToString().Length > 0)
	result = sb.ToString().TrimEnd(',');

sqlResult="Select 0 as ID,GradeSubjectNo,GradeSubjectName,ClassNo,ClassName,StudentNo,Name,Sum(isnull(Credit,0)) as 总学分,Sum(isnull(Credit,0) * isnull(ScoreDot,0) ) as  总学分绩点,( Sum(isnull(Credit,0)*isnull(ScoreDot,0))/( case   Sum(IsNull(Credit,0)) when  0 then 1 else  Sum(IsNull(Credit,0)) end )) as 平均学分绩点,Count(*) as 所修门数, {0} from vscoScoreLast where {1} group by StudentNo,Name,ClassNo,ClassName,GradeSubjectNo,GradeSubjectName";

sqlResult=string.Format(sqlResult, result, this.WhereClause);