- SQL code
select stuAscourse.addtime as 日期,stuascourse.courseid as 班级代码,stuascourse.studentid as 听课证代码,stuAscourse.sname as 姓名,stuascourse.sex as 性别,course.coursename as 班级名称 ,course.removeclassfee as 报名费,stuAscourse.Tuition as 学费,stuAscourse.bookfee as 书费 ,stuascourse.retainfee as 上期保留费用,stuascourse.discountfee as 优惠金额,stuascourse.changeclassfee as 转班费,stuascourse.otherfee as 其它费用,stuAscourse.telephone as 手机,stuascourse.idcard as 身份证号,stuascourse.home as 家庭住址,stuascourse.workplace as 工作单位,stuascourse.addplace as 报名地点,stuascourse.adminid as 收银员ID,stuascourse.neworold as 新生OR老生,stuascourse.infosrc as 信息来源,stuAscourse.oldtename as 原老师 ,stuAscourse.oldclassid as 原班级,stuascourse.R_bookflag as 是否领书,stuascourse.S_bookname as 发书人,stuascourse.S_booktime as 发书时间,course.content as 上课时间,course.begintime as 开课时间,course.classroom as 上课地点,course.tuition as 标准费用,course.classhour as 课时数,course.maxnumber as 限定人数,course.alarmnumber as 预警人数,course.leavetime as 退班期限,stuascourse.contents as 备注 from stuascourse INNER JOIN course ON stuAsCourse.courseid = course.courseid where (stuascourse.delflag <>'已退班') and stuascourse.addtime between '2007-12-6 0:00:00' and '2008-1-23 0:00:00'
上面是我程序中的SQL语句,程序运行到这条查询的时候很慢,要等30秒到一分钟的样子..不知道问题出在哪,,希望高手帮忙优化下.
------解决方案--------------------
- SQL code
create index idx_stuascourse_addtime on stuascourse(addtime)create index idx_stuascourse_delflag on stuascourse(delflag)select stuAscourse.addtime as 日期 ,stuascourse.courseid as 班级代码 ,stuascourse.studentid as 听课证代码 ,stuAscourse.sname as 姓名 ,stuascourse.sex as 性别 ,course.coursename as 班级名称 ,course.removeclassfee as 报名费 ,stuAscourse.Tuition as 学费 ,stuAscourse.bookfee as 书费 ,stuascourse.retainfee as 上期保留费用 ,stuascourse.discountfee as 优惠金额 ,stuascourse.changeclassfee as 转班费 ,stuascourse.otherfee as 其它费用 ,stuAscourse.telephone as 手机 ,stuascourse.idcard as 身份证号 ,stuascourse.home as 家庭住址 ,stuascourse.workplace as 工作单位 ,stuascourse.addplace as 报名地点 ,stuascourse.adminid as 收银员ID ,stuascourse.neworold as 新生OR老生 ,stuascourse.infosrc as 信息来源 ,stuAscourse.oldtename as 原老师 ,stuAscourse.oldclassid as 原班级 ,stuascourse.R_bookflag as 是否领书 ,stuascourse.S_bookname as 发书人 ,stuascourse.S_booktime as 发书时间 ,course.content as 上课时间 ,course.begintime as 开课时间 ,course.classroom as 上课地点 ,course.tuition as 标准费用 ,course.classhour as 课时数 ,course.maxnumber as 限定人数 ,course.alarmnumber as 预警人数 ,course.leavetime as 退班期限 ,stuascourse.contents as 备注 from stuascourse INNER JOIN course ON stuAsCourse.courseid = course.courseid where not exists (select 1 from stuascourse c where delflag='已退班' and c.courseid=stuAsCourse.courseid)and stuascourse.addtime between '2007-12-6 0:00:00' and '2008-1-23 0:00:00'
------解决方案--------------------
如果有索引 ，那么索引影响查询多半出现在索引的统计信息不正确或者碎片过多,无法正确评估查询成本或导致过多的I/O, 影响数据读取性能
------解决方案--------------------
stuascourse.addtime
stuAsCourse.courseid
course.courseid
这3个字段最好都建索引。
------解决方案--------------------
寫法思路有問題。
根據樓主的意思，應該是查找在StuasCourse表裡在一段時間內沒有 已退班 的人
建議先Left Join StuasCourse SC Where SC.CourseID is Null
再 Join StuasCourse 。
估計DelFlag的候選值很少，且AddTime值很多，所以該一個Index (AddTime,DelFalg)
建議寫代碼將首字母大寫，看起來直觀。。。。全是小寫，看得頭痛，個人習慣