asa的in效率分析

 in 比 直接关联效率高
如：
select g.examid as examid,
       g.code_course as code_course,
       i.sackid as sackid,
       g.permitid as permitid,
       g.name_cn as name_cn,
       g.inputno as inputno,
       g.operator as operator,
       convert(integer,g.smallid) as smallid,
       g.grademark as grademark
from exam_grade_small g,
     exam_stusackinfo i
where  g.examid = 1
  and g.code_course = '002'
  and (g.operator='9999' or g.operator='8888')
  and g.examid = i.examid
  and g.code_course = i.code_course
  and g.permitid between i.startpermitid and i.endpermitid
  and exists(
select 1 from
(select a.examid,a.code_course,a.permitid,a.smallid
  from exam_grade_small a
 where a.examid = 1
   and a.code_course = '002'
   and (a.operator='9999' or a.operator='8888')
group by a.examid,a.code_course,a.permitid,a.smallid
 having count(distinct a.grademark) > 1 or count(distinct a.operator) = 1) h
where
g.examid = h.examid
  and g.code_course = h.code_course
  and g.permitid = h.permitid
  and g.smallid = h.smallid
)
--执行时间0.078秒

select g.examid as examid,
       g.code_course as code_course,
       i.sackid as sackid,
       g.permitid as permitid,
       g.name_cn as name_cn,
       g.inputno as inputno,
       g.operator as operator,
       convert(integer,g.smallid) as smallid,
       g.grademark as grademark
from exam_grade_small g,(select a.examid,a.code_course,a.permitid,a.smallid
  from exam_grade_small a
 where a.examid = 1
   and a.code_course = '001'
   and (a.operator='9999' or a.operator='8888')
group by a.examid,a.code_course,a.permitid,a.smallid
 having count(distinct a.grademark) > 1 or count(distinct a.operator) = 1) h,
     exam_stusackinfo i
where g.examid = h.examid
  and g.code_course = h.code_course
  and g.permitid = h.permitid
  and g.smallid = h.smallid
  and g.examid = 1
  and g.code_course = '001'
  and (g.operator='9999' or g.operator='8888')
  and h.examid = i.examid
  and h.code_course = i.code_course
  and h.permitid between i.startpermitid and i.endpermitid

--执行时间超过30秒