如何优化此SQL语句？

[joanagle]

有表storedata, storecode是主键；表rscollectdata，storecode+nf才是唯一的。
现在要显示表storedata的字段值，并且这个storecode是在201104月里存在，并且在201105月里不存在的。
SQL语句如下：

select distinct cityn, chain, storecode, storedesc from storedata
  where storecode in (select storecode from rscollectdata 
  where nf='201104'
   and storecode not in (select storecode from rscollectdata 
   where nf ='201105')) order by cityn, chain, storecode

这段SQL语句在SQL查询器是时间近乎一分钟，但是在程序里运行时却提示“超时已过期”
说明：目前表rscollectdata的数据量大约在10w左右，以后每月都会增加，每月增加量在2，3k左右。

[mingpei0703]

没建索引吗？

[-晴天]

try:

select distinct cityn, chain, storecode, storedesc 
from storedata a
where exists(select 1 from rscollectdata where nf='201104' and storecode=a.storecode)
and not exists(select 1 from rscollectdata where nf ='201105' and storecode=a.storecode)
order by cityn, chain, storecode

[GoAwayZ]

select distinct cityn, chain, storecode, storedesc 
 from storedata a 
 inner join rscollectdata b on a.storecode=b.storecode and b.nf='201104'
 left join rscollectdata c on a.storecode=c.storecode and c.nf='201105'
where c.storecode is null

[zs621]

[code=SQL]
select distinct cityn, chain, storecode, storedesc from storedata a where exists  (select 1 from rscollectdata where storecode=a.storecode and  nf='201104') and not exists (select 1from rscollectdata where storecode=a.storecode and nf ='201105')) order by cityn, chain, storecode

[/code]

[--小F--]

改成相关子查询

[--小F--]

select
 distinct cityn, chain, storecode, storedesc 
from
 storedata a 
join
 rscollectdata b 
on
 a.storecode=b.storecode and b.nf='201104'
left join rscollectdata c 
on
 a.storecode=c.storecode and c.nf='201105'
where
 c.storecode is null

[xuexiaodong2009]

[Quote=引用 4 楼 zs621 的回复:]

SQL code
[code=SQL]
select distinct cityn, chain, storecode, storedesc from storedata a where exists (select 1 from rscollectdata where storecode=a.storecode and nf='201104') and not exists (selec……
[/Quote]要添加索引的

[Josen_]

第一.建个索引.
第二.照6楼子查询.

PS:大数据量查询最好不要用in,这个速度比较慢

[joanagle]

呵呵，谢谢大家！已经采用了qianjin036a的方法，速度很快

[joanagle]

呵呵，经过测试，我的原有SQL语句执行时间需要42秒，而qianjin036a的代码只需要1秒，
zy112429的需要2秒，zs621的也是只需要1秒，fredrickhu的也是需要2秒。

以上测试都是在SQL查询器上的运行时间。

再次谢谢大家！CSDN高手云集呀！

[joanagle]

另外，我另一贴准备结贴，但是没人回答，谁有空就去答一下，分全给了
http://topic.csdn.net/u/20110304/14/e68f9136-4534-481f-8f3e-cb98df4bb428.html

[kevinjay567]

1，避免用not in,in的写法，改用inner join等连接查询
2，对查询的column 加index