关于"按某列分组，求最新一条记录"两种sql写法的测试

[nzperfect]

-----------------------------------------------------------------------
--组织测试数据,首先我们来组织category分类比较少的情况,分了体现这个差别，所以只分了两个分类
    drop table tb
    create table tb(id int identity primary key,category nvarchar(50),remark char(5000))


    declare @i int set @i=1
    while @i<=5000
    begin
        insert into tb select 'a','remark'+rtrim(@i)
        set @i=@i+1
    end

    declare @i int set @i=1
    while @i<=5000
    begin
        insert into tb select 'b','remark'+rtrim(@i)
        set @i=@i+1
    end


    
    checkpoint    
    dbcc dropcleanbuffers
    dbcc freeproccache
    dbcc freesystemcache ('all') 
    set statistics io on
    set showplan_all on
    set showplan_all off

--在不给category加索引的情况下：
    select * from tb t where not exists (select 1 from tb where category = t.category and id>t.id)
    /*
    (2 行受影响)
    表 'tb'。扫描计数 6，逻辑读取 22654 次，物理读取 5 次，预读 3900 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。    */
    select * from tb t
    inner join
    (
        select max(id) as id from tb group by category
    ) as t2 on t.id=t2.id
    /*
    (2 行受影响)
    表 'tb'。扫描计数 1，逻辑读取 10044 次，物理读取 3 次，预读 10016 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    */


--在给category加索引的情况下：
    create index ix_01 on tb (category) update statistics tb with fullscan 
    select * from tb t where not exists (select 1 from tb where category = t.category and id>t.id)
    /*
    (2 行受影响)
    表 'tb'。扫描计数 10001，逻辑读取 31377 次，物理读取 7 次，预读 10031 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。    */
    select t.* from tb t
    inner join
    (
        select max(id) as id from tb group by category
    ) as t2 on t.id=t2.id
    /*
    (2 行受影响)
    表 'tb'。扫描计数 1，逻辑读取 28 次，物理读取 6 次，预读 20 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    */


-----------------------------------------------------------------------
--组织测试数据,首先我们来组织category分类比较多的情况,2001类
    drop table tb
    create table tb(id int identity primary key,category nvarchar(50),remark char(5000))


    declare @i int set @i=1
    declare @ii int set @ii=1
    while @i<=10000
    begin
        if @i%5=0 set @ii=@ii+1
        insert into tb select 'a'+rtrim(@ii),'remark'+rtrim(@i)        
        set @i=@i+1
    end

    checkpoint
    dbcc dropcleanbuffers
    dbcc freeproccache
    dbcc freesystemcache ('all') 
    set statistics io on
    set showplan_all on
    set showplan_all off

--在不给category加索引的情况下：
    select * from tb t where not exists (select 1 from tb where category = t.category and id>t.id)
    /*
    (2001 行受影响)
    表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'tb'。扫描计数 2，逻辑读取 20076 次，物理读取 1 次，预读 4147 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    */
    select * from tb t
    inner join
    (
        select max(id) as id from tb group by category
    ) as t2 on t.id=t2.id
    /*
     (2001 行受影响)
    表 'tb'。扫描计数 1，逻辑读取 16175 次，物理读取 3 次，预读 10025 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    */


--在给category加索引的情况下：
    create index ix_01 on tb (category) update statistics tb with fullscan 
    select * from tb t where not exists (select 1 from tb where category = t.category and id>t.id)
    /*
    (2001 行受影响)
    表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    表 'tb'。扫描计数 2，逻辑读取 10069 次，物理读取 4 次，预读 10045 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    */
    select t.* from tb t
    inner join
    (
        select max(id) as id from tb group by category
    ) as t2 on t.id=t2.id
    /*
    (2001 行受影响)
    表 'tb'。扫描计数 1，逻辑读取 10694 次，物理读取 2 次，预读 10053 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
    */


-----
    总结：
    在用来分组的字段可分组比较少的情况，用gorup by 分组后再inner join原表的效率会明显高于not exists写法,
    因为group by 后再inner join 原表是采用 hash join ，这要在有分组列有索引的情况下，已经排序，数据又少，速度极快。
    但not exits是nested loop join,在这种有大量外部数据输入再比较的情况下，效率是很慢的。

    反之,两者效率相当,都会很慢。

[一品梅]

学习PA

[水族杰纶]

收藏学习之~~~

[nzperfect]

晕，更正下，上面打错了，是 merge join .

-----  
总结： 在用来分组的字段可分组比较少的情况，用gorup by 分组后再inner join原表的效率会明显高于not exists写法, 因为group by 后再inner join 原表是采用 merge join ，这要在有分组列有索引的情况下，已经排序，数据又少，速度极快。 但not exits是nested loop join,在这种有大量外部数据输入再比较的情况下，效率是很慢的。 反之,两者效率相当,都会很慢。

[yrwx001]

頂

[-狙击手-]

[Quote=引用 3 楼 yrwx001 的回复:]
頂
[/Quote]

[Garnett_KG]

20太小气了.

[nzperfect]

[Quote=引用 5 楼 Garnett_KG 的回复:]
20太小气了.
[/Quote]
哈哈.. 赚点分不容易啊 ~

[M1CR0S0FT]

[Quote=引用 2 楼 perfectaction 的回复:]
晕，更正下，上面打错了，是 merge join .


SQL code-----
总结： 在用来分组的字段可分组比较少的情况，用gorup by 分组后再inner join原表的效率会明显高于not exists写法, 因为group by 后再inner join 原表是采用 merge join ，这要在有分组列有索引的情况下，已经排序，数据又少，速度极快。 但not exits是nested loop join,在这种有大量外部数据输入再比较的情况下，效率是很慢的。 反之,两者效率相当,都会很慢。
[/Quote]
还是有问题.

[nzperfect]

[Quote=引用 7 楼 M1CR0S0FT 的回复:]
引用 2 楼 perfectaction 的回复:
晕，更正下，上面打错了，是 merge join .


SQL code-----
总结： 在用来分组的字段可分组比较少的情况，用gorup by 分组后再inner join原表的效率会明显高于not exists写法, 因为group by 后再inner join 原表是采用 merge join ，这要在有分组列有索引的情况下，已经排序，数据又少，速度极快。 但not exits是nested loop join,在这种有大量外部数据输入再比较的情况下，效率是很慢的。…
[/Quote]

什么问题？

[M1CR0S0FT]

[Quote=引用 8 楼 perfectaction 的回复:]
引用 7 楼 M1CR0S0FT 的回复:
引用 2 楼 perfectaction 的回复:
晕，更正下，上面打错了，是 merge join .


SQL code-----
总结： 在用来分组的字段可分组比较少的情况，用gorup by 分组后再inner join原表的效率会明显高于not exists写法, 因为group by 后再inner join 原表是采用 merge join ，这要在有分组列有索引的情况下，已经排序，数据又少，速度极快。 但not exits是nested loop join,在这种有大量外部数据输…
[/Quote]
gorup by---group by

[nzperfect]

[Quote=引用 9 楼 M1CR0S0FT 的回复:]
引用 8 楼 perfectaction 的回复:
引用 7 楼 M1CR0S0FT 的回复:
引用 2 楼 perfectaction 的回复:
晕，更正下，上面打错了，是 merge join .


SQL code-----
总结： 在用来分组的字段可分组比较少的情况，用gorup by 分组后再inner join原表的效率会明显高于not exists写法, 因为group by 后再inner join 原表是采用 merge join ，这要在有分组列有索引的情况下，已经排序，数据又少，速度极快。 但not exits是nested lo…
[/Quote]

噢。果然

[hery2002]

呵呵,
今天早上在一个帖子里面才回复了,
没有时间测试,
没想到P.A居然就测试出来了。
谢谢完美MM.:)

[M1CR0S0FT]

[Quote=引用 11 楼 hery2002 的回复:]
呵呵,
今天早上在一个帖子里面才回复了,
没有时间测试,
没想到P.A居然就测试出来了。
谢谢完美MM.:)
[/Quote]
楼主是女人?