sqlserver 存储过程中使用临时表到底会不会导致重编译

最新推荐文章于 2023-06-13 09:01:59 发布

dz45693

最新推荐文章于 2023-06-13 09:01:59 发布

阅读量3.5k

点赞数

分类专栏： MSSQl数据库

MSSQl数据库专栏收录该内容

30 篇文章 0 订阅

订阅专栏

曾经在网络上看到过，SqlServer的存储过程中使用临时表，会导致执行计划无法重用，

运行时候会导致重编译的这么一个说法，自己私底下去做测试的时候，根据profile的跟踪结果，

如果不是统计信息变更导致导致的重编译，单单是使用临时表，并不会导致重编译，

但是对于一些特殊的情况，又确实会出现重编译的，

为了弄清楚这个问题，查阅了大量的资料，才把这个问题弄清楚，这里特意记录下来，希望武断地认为存储过程中使用了临时表就会导致重编译的这个观点得到纠正。

首先进行下面的测试，我们知道，导致临时表重编译的因素之一就是统计信息的变化，统计信息的变化依赖于往临时表中写入的数据量，

首选我要控制插入临时表中的数据量不超过统计信息更新而导致重编译的阀值，先排除统计信息的变更导致重编译，

看看仅仅是多次运行SP，是否因为存储过程中有了临时表而会产生重编译

 
        --首选创建一个表，供存储过程中测试使用 
       
        create  
        table  
        test1 
       
        ( 
       
        id  
        int  
        identity(1,1), 
       
        name  
        varchar 
        (50) 
       
        ) 
       
        --插入10000条测试数据 
       
        insert  
        into  
        test1  
        values  
        (NEWID()) 
       
        go 10000 
       
        --创建一个存储过程，其中存储过程中定义了一个临时表，根据参数，往临时表中写入数据 
       
        create  
        proc testRecompile(@i  
        int 
        ) 
       
        as 
       
        begin 
       
        create  
        table  
        #t (id  
        int 
        , 
        name  
        varchar 
        (50)) 
       
        insert  
        into  
        #t  
        select  
        id, 
        name  
        from  
        test1  
        where  
        id<@i 
       
        select  
        *  
        from  
        #t 
       
        end

那么就开始运行这个SP，然后监控profile，看看第一次运行，以及除了第一次运行之后，到底有没有发生重编译

 
        --第一次运行，代入参数1 
       
        exec  
        testRecompile 1 
       
        --第二次运行，代入参数2 
       
        exec  
        testRecompile 2

下面是profile的截图，可以很清楚地看到，第一次运行之后，再次运行SP的时候，没有发生重编译的动作，也就是说重用了第一次的执行计划缓存

这里解释两个问题，

1，第一次运行的时候，为什么不是因为架构更改导致的重编译，而是Deferred Complie？

2，第二次运行的时候，为什么没有重编译，因为临时表是每次运行的时候创建的啊，肯定是更改了架构（change schema）了，为什么没有重编译？

首先，说明第一个问题，

1，第一次运行的时候，当存储过程testRecompile编译的时候，

　　插入语句（insert into #t select id,name from test1 where id<@i）和查询语句（select * from #t），

　　因为#t表还没有被创建，因为这两句并没有被编译，

　　编译的时候的执行计划并没有完全完成，

　　当这个存储过程执行的时候，临时表才被创建，此时才真正的开始编译临时表对象的语句，这个编译的过程是执行的时候完成的，而不是纯粹的编译阶段完成的

　　所以这是Deferred Complie，也即是运行时才进行的编译，就是所谓的延迟编译（Deferred Complie）。

2，第二个问题，重新运行临时表的时候，按道理，因为创建了临时表，必然导致架构的更改，为什么没有重编译？

　　这个是因为，存储过程中使用了临时表，对临时表的使用是引用其“名称”（比如这里的#t），而非ID（从临时数据库中查询sys.sysobjects）

　　虽然多个会话同时运行这个SP的话，每个会话都会生成一个临时表，每个会话生成的临时表的ID都是不同的，

　　但是要注意的是，存储过程中并没有直接使用临时表对象的ID，而是临时表名字本身，

　　第一次运行之后，缓存的执行计划与第二次运行时一样的，所以第二次运行这个SP可以重用这个第一次生成的执行计划，

上面说了，在某些情况下，存储过程中使用临时表会导致重编译，这是在什么情况下发生的呢？

因为在某些情况下，要先生成临时表，然后以动态sql的方式去执行一段有临时表参与的sql，此时对于临时表的引用是引用其ID，而不是名称

这个要归结于对于临时表的调用方式，当存储过程中定义了临时表，用exec或者是sp_executesql的方式调用的时候，这两种执行sql的方式相当于新建了会话，

此时因为不同回话之间，同一个临时表生成的ID是不同的，此时才会导致存储过程中发生sechme change的重编译

上代码

 
        create  
        proc testRecompile2(@i  
        int 
        ) 
       
        as 
       
        begin 
       
        create  
        table  
        #t (id  
        int 
        , 
        name  
        varchar 
        (50)) 
       
        insert  
        into  
        #t  
        select  
        id, 
        name  
        from  
        test1  
        where  
        id<=@i 
       
        exec 
        ( 
        'select * from #t' 
        ) 
       
        end 
       
        DBCC FREEPROCCACHE 
       
        --第一次运行，代入参数1 
       
        exec  
        testRecompile2 1 
       
        --第二次运行，代入参数2 
       
        exec  
        testRecompile2 2