这条sql语句如何优化?

[lrest]

系统：vb+access,piii 800
z_arrange_list 记录数 496
z_class 记录 28
z_attend_clock 记录 1900

方案一：
SELECT aa.empl_id, aa.c_day, aa.on_duty, aa.off_duty
from (SELECT a.empl_id, day(a.t_date) AS c_day, b.card_begin, b.on_duty, b.off_duty, b.card_end
FROM z_arrange_list AS a INNER JOIN z_class AS b ON a.class_id=b.class_id) aa
WHERE not (aa.empl_id & aa.c_day & aa.on_duty & off_duty) in
(select card_id & c_day & on_duty & off_duty from z_attend_clock where valid_duty=true and year_mon=200407 group by card_id,c_day, on_duty,off_duty)

执行时间 3分钟以上

方案二：
创建１个临时表
1、SELECT a.empl_id, day(a.t_date) AS c_day, b.card_begin, b.on_duty, b.off_duty, b.card_end into Tmp01 FROM z_arrange_list AS a INNER JOIN z_class AS b ON a.class_id=b.class_id

2、SELECT aa.empl_id, aa.c_day, aa.on_duty, aa.off_duty
from tmp01 aa
WHERE not (aa.empl_id & aa.c_day & aa.on_duty & off_duty) in
(select card_id & c_day & on_duty & off_duty from z_attend_clock where valid_duty=true and year_mon=200407 group by card_id,c_day, on_duty,off_duty)

*执行时间 1.1分

方案三：
创建2个临时表
1、SELECT a.empl_id, day(a.t_date) AS c_day, b.card_begin, b.on_duty, b.off_duty, b.card_end into Tmp01 FROM z_arrange_list AS a INNER JOIN z_class AS b ON a.class_id=b.class_id
2、select (card_id & c_day & on_duty & off_duty) as sect into Tmp02 from z_attend_clock where valid_duty=true and year_mon=200407 group by card_id,c_day, on_duty,off_duty

3、SELECT aa.empl_id, aa.c_day, aa.on_duty, aa.off_duty
from tmp01 aa
WHERE not (aa.empl_id & aa.c_day & aa.on_duty & off_duty) in
(select sect from tmp02)

*执行时间 25秒

'************************************
感觉速度太慢,谁能给我一个好的优化方案

[guolibin]

主要是NOT IN的速度慢，改为LEFT JOIN应该要快些

[lrest]

to guolibin:
对，我现在是用 表１ left join 表2
然后列出 表１中表２的列为null值的行。