select count(1) from t_student
left join t_class on f_classId=t_class.f_Id
left join t_schoolInfo on t_class.f_schoolId=t_schoolInfo.f_Id
left join t_grade on t_grade.f_graduateYear=(convert(int,t_class.f_graduateYear)-2012)
where 1=1 and t_schoolInfo.f_prefectureId=15
我测试时应该是这里慢
(convert(int,t_class.f_graduateYear)-2012)
我把这里改成固定值时就很快。大家帮忙分析下原因。
left join t_class on f_classId=t_class.f_Id
left join t_schoolInfo on t_class.f_schoolId=t_schoolInfo.f_Id
left join t_grade on t_grade.f_graduateYear=(convert(int,t_class.f_graduateYear)-2012)
where 1=1 and t_schoolInfo.f_prefectureId=15
我测试时应该是这里慢
(convert(int,t_class.f_graduateYear)-2012)
我把这里改成固定值时就很快。大家帮忙分析下原因。
left join t_class on f_classId=t_class.f_Id
left join t_schoolInfo on t_class.f_schoolId=t_schoolInfo.f_Id
left join t_grade on t_grade.f_graduateYear+2012=t_class.f_graduateYear
where 1=1 and t_schoolInfo.f_prefectureId=15
SQL 会额外花费时间去处理运算结果的而且你又连接了很多的Table ,当然会很慢了。
left join (Select *,Cast(f_graduateYear As int)-2012 As f_graduateYear_2 From t_class) As t on f_classId=t.f_Id
left join t_schoolInfo on t.f_schoolId=t_schoolInfo.f_Id
left join t_grade on t_grade.f_graduateYear=t.f_graduateYear
where 1=1 and t_schoolInfo.f_prefectureId=15
left join (Select *,Cast(f_graduateYear As int)-2012 As f_graduateYear_2 From t_class) As t on f_classId=t.f_Id
left join t_schoolInfo on t.f_schoolId=t_schoolInfo.f_Id
left join t_grade on t_grade.f_graduateYear=t.f_graduateYear_2
where 1=1 and t_schoolInfo.f_prefectureId=15