none
performance impact of using temp tables in stored procedures using Sql Server RRS feed

  • Question

  • Hi Guys  ,

    I would like to know the performance impact of using temp tables  in stored procedures using Sql Server . I believe temp table cause SP recompilation.

    I would like to why and when this causes the stored procedure to recompile? I also want to  know the improvement in SQL 2012 or Greater version .
    Also, what are your thoughts on creating an encrypted SP that queries encrypted data? Is this a good/bad way and why..?
    will appreciate your help
    Thank you.
    Shrikant
    Monday, September 23, 2019 5:53 AM

All replies

  • >>>I would like to why and when this causes the stored procedure to recompile? I also want to  know the >>>improvement in SQL 2012 or Greater version .

    Here you go

    https://blogs.msdn.microsoft.com/psssql/2011/07/15/stored-procedure-recompile-caused-by-alter-table-on-temp-table/

    MS has implement table cache mechanism 

    to reduce the costs associated with 
    temp table creation. The second temp table creation is much faster. Instead 
    of dropping and creating the table we simply truncate it. All indexes and 
    statistics are truncated and identity column values are reset. The following script demonstrates the performance impact of temp table 
    cache. Lets execute two stored procs in a loop (10000 times). The temp 
    table in the first stored proc is not cached (because the index is created 
    outside of the CREATE TABLE statement). The temp table in the second stored 
    proc is cached. On my box, the execution of the first stored proc takes 26 
    seconds. The execution of the second one (caching) takes only 5 seconds. 


    use tempdb 
    go 


    CREATE PROCEDURE no_caching AS 
     CREATE TABLE #temp (a int NOT NULL) 
     CREATE UNIQUE INDEX idx on #temp (a) 
     INSERT #temp (a) VALUES (23) 
     DROP TABLE #temp 
    go 


    CREATE PROCEDURE caching AS 
     CREATE TABLE #temp (a int NOT NULL unique) 
     INSERT #temp (a) VALUES (23) 
     DROP TABLE #temp 
    go 


    declare @starttime datetime set @starttime=getdate(); 
    declare @i int set @i = 0; 
    while (@i<10000) 
    begin 
     set @i=@i+1; 
     exec no_caching; 
    end; 
    declare @endtime datetime set @endtime=getdate(); 
    select datediff( second, @starttime, @endtime ) as 'no caching'; 
    go 


    declare @starttime datetime set @starttime=getdate(); 
    declare @i int set @i = 0; 
    while (@i<10000) 
    begin 
     set @i=@i+1; 
     exec caching; 
    end; 
    declare @endtime datetime set @endtime=getdate(); 
    select datediff( second, @starttime, @endtime ) as 'caching'; 
    go 


    DROP PROCEDURE caching; 
    DROP PROCEDURE no_caching; 
    go 

    >>>Also, what are your thoughts on creating an encrypted SP that queries encrypted data? Is this a good/bad way >>>and why..?

    If you are using SQL Server 2016 take a look into

    https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine?view=sql-server-2017


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, September 23, 2019 6:57 AM
    Moderator

  • I would like to know the performance impact of using temp tables  in stored procedures using Sql Server . I believe temp table cause SP recompilation.

    People around world heavily uses temp tables and they work just fine if you have configured your tempdb optimally I do not see you would face any slowness.


    I would like to why and when this causes the stored procedure to recompile? I also want to  know the improvement in SQL 2012 or Greater version .
    Also, what are your thoughts on creating an encrypted SP that queries encrypted data? Is this a good/bad way and why..?
    For what can cause SP to recompile see the stackoverflow answer. I am not so sure about encrypted SP, have not worked with them much.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, September 23, 2019 8:03 AM
    Moderator
  • Hi Shrikant,

    >>I would like to why and when this causes the stored procedure to recompile?

    Please check whether below link could help you. Optimizing SQL Server Stored Procedures to Avoid Recompiles.

    >>what are your thoughts on creating an encrypted SP that queries encrypted data? 

    Please refer to Encrypt the stored procedure in SQL server and Encrypting Stored Procedure, View & Function In SQL Server.

    Hope this could help you.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, September 24, 2019 8:14 AM