• 优化SQL 语句 in 和not in 的替代方案

    普通类
    • 支持
    • 批判
    • 提问
    • 解释
    • 补充
    • 删除
    • 来源一:http://www.gdwg.net/edu/sjk/mssql/2010/0529/83632_2.html

    (SELECT Info.id,Info.PName, Info.remark, Info.impdate,Info.upstate, bakInfo.id AS bakID
    FROM Info left JOIN
    bakInfo ON Info.id = bakInfo.id ) as t
    where t.bakID is null and t.upstate=0
    GO
    SET STATISTICS TIME OFF;

     

      此操作执行时间:

    SQL Server 分析和编译时间:
    CPU 时间 = 247 毫秒,占用时间 = 247 毫秒。

    SQL Server 执行时间:
    CPU 时间 = 406 毫秒,占用时间 = 475 毫秒。

    (100000 行受影响)
    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

      --更改当前表状态
    update Info set upstate=1
    FROM Info INNER JOIN
    bakInfo ON Info.id = bakInfo.id
      此操作执行时间:

    SQL Server 分析和编译时间:
    CPU 时间 = 4 毫秒,占用时间 = 4 毫秒。

    SQL Server 执行时间:
    CPU 时间 = 219 毫秒,占用时间 = 259 毫秒。

    (100000 行受影响)
    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

      --删除当前表数据

    delete from Info
    FROM Info INNER JOIN
    bakInfo ON Info.id = bakInfo.id
    where Info.upstate=1
      此操作执行时间:

    SQL Server 分析和编译时间:
    CPU 时间 = 177 毫秒,占用时间 = 177 毫秒。

    SQL Server 执行时间:
    CPU 时间 = 219 毫秒,占用时间 = 550 毫秒。

    (100000 行受影响)
    SQL Server 分析和编译时间:
    CPU 时间 = 0 毫秒,占用时间 = 1 毫秒。

      可以看出使用join方案比使用not in 和in执行时间要短很多了

    • 来源二:http://www.cnblogs.com/bbcw/archive/2005/02/04/101684.html

    前言:

    今天在写一条sql查询语句,其需要从一个表A中返回所有A不再表B中的结果集,当然,这种实现最方便的方法就是用NOT IN。

    如:select a.* from a where a.id not in (select id from b where…..)

    我们大家都知道很多sql方面的文章都建议大家尽量不要使用NOT IN的方法,因为这种方法的效率不高。那有没有替代的办法呢?(声明因为当时的情况要求不能使用储存过程,所以只有写sql语句)和同事实验了一下,结果用以下方法实现了。

     

    目的:

    替换NOT IN 方法。

    说明:

    在单条SQL语句中,不使用储存过程,不使用临时表。使用存储过程和临时表不再本文的讨论范围中。

    实现:

    例:

    表aa:结构

    id value ……

    1 a

    2 b

    3 c

    4 d

    5 e

    6 f

    ---------------------------------------------------------------------------------------------

     

    表bb:结构

    id ……

    2

    4

    6

     

    现在我要取表aa里的所有字段,条件是aa的id值不在bb的id值当中(not in)。也就是应该返回所有id为奇数的字段

     

    使用NOT IN的SQL:

    select * from aa where id not in(select id from bb)

    就一条语句,简单明了,可惜效率不高,而且公司规范要求尽量不用NOT IN,害我费了好大事crying……

     

    改造后的SQL:

    select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null

     

    解释一下。在开始时候我最早想用内联表的方式,可是无论如何也每找到一个好的办法,干脆就是实现不了。(大家有好办法指教先。)

    后来自己考虑了一下NOT IN的逻辑,A NOT IN B就是说A是主体,B起到的之不过是一个判断作用,我们可以先把所有符合条件的A记录全部查询出来而不管他是否属于B,然后再从这里剔除值同时属于B的部分。

    Select aa.* from aa

    但是仅仅这样是不够的,我们无法利用这个返回的结果集判断是否属于B并排除它,为此,我想到构造一个临时的列,这个列的值应该是在A的结果集范围内,所有在B中的值。而这个结果集的主体应该是所有满足先决条件的A,然后加上满足条件的B,而不满足条件的B值则不再考虑范围内,所以用了left join。

    这一段是关键,不知道我阐述清楚了没有,没明白的继续看

    于是就出来这一句。

    select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id

    没看明白上面的看结果集就明白了

    id value tempcolum

    ---------------------------------------------

    1 a NULL

    2 b 2

    3 c NULL

    4 d 4

    5 e NULL

    6 f 6

     

    看到这个结果集我想大家都明白我的意思了吧。对了,我们就是要对这个结果集进行二次操作。

    相信大家都看到了,生成的这个结果集包含了所有符合条件的表aa字段和bb的id,如果aa中的值在bb中,则tempcolum的值就不会为null,如果不在就是null,这样我们只需要从这个结果集里查询所有tempcolum值为null的就可以满足我们的要求了

    所以最终的sql出来了

    select cc.id,cc.value from (select aa.*,bb.id as tempcolum from aa left join bb on aa.id=bb.id) as cc where cc.tempcolum is null

    我们只需要id和value两个字段,其他的就不要了。

    结果

    id value

    -------------------------

    1 a

    3 c

    5 e

     

    ok,实现了,希望对大家有帮助。

    声明:

    这个方法的效率我并没有测试,大家感兴趣的可以自己测一下,呵呵,看看是不是比NOT IN的方法好,如果不好的话,还不如用NOT IN呢。同时感谢和我一起实验的同事。

    最后说一句,文笔不好,表述不清楚的地方请见谅.

    • 标签:
    • aa
    • 方法
    • bb
    • 时间
    • select
    • 使用
    • 所有
    • 毫秒
    • id
    • sql
  • 加入的知识群:
    学习元评论 (0条)

    评论为空
    聪明如你,不妨在这 发表你的看法与心得 ~



    登录之后可以发表学习元评论
      
暂无内容~~
顶部