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

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

--组织测试数据,首先我们来组织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,在这种有大量外部数据输入再比较的情况下，效率是很慢的。



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