none
Insert into a temp table is to slow

    Question

  • i'd like to know why if i created a temp table out of my procedure the insert into it get slower than if i create that temp table inside my procedure. follows an example:

    create table #Test (col1 varchar(max))
    go
    create proc dbo.test
    as
    begin
        truncate table #Test
        insert into #Test
        select 'teste'
        FROM sys.tables
        cross join sys.columns
    end
    go
    exec dbo.test
    go
    
    create table #Test2 (col1 varchar(max))
    go
    truncate table #Test2
    insert into #Test2
    select 'teste'
    FROM sys.tables
    cross join sys.columns

    At test, we get duration 71700, reads 45220, CPU 26052 At test2, we get duration 49636, reads 45166, cpu 24960

    best regards

    Friday, April 11, 2014 8:24 PM

Answers

  • There should be no difference.

    You would have to repeat the test you designed a few times to take readings and then reverse the order.

    BOL: " Benefits of Using Stored Procedures


            

    The following list describes some benefits of using procedures.

    Reduced server/client network traffic          

    The commands in a procedure are executed as a single batch of code. This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

    Stronger security          

    Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. The procedure controls what processes and activities are performed and protects the underlying database objects. This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

    The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.

    When calling a procedure over the network, only the call to execute the procedure is visible. Therefore, malicious users cannot see table and database object names, embed Transact-SQL statements of their own, or search for critical data.

    Using procedure parameters helps guard against SQL injection attacks. Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQL statement(s) inside the procedure and compromise security.

    Procedures can be encrypted, helping to obfuscate the source code. For more information, see SQL Server Encryption.

    Reuse of code          

    The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

    Easier maintenance          

    When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

    Improved performance          

    By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

    If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. In this case, recompiling the procedure and forcing a new execution plan can improve performance. "

    LINK: http://technet.microsoft.com/en-us/library/ms190782.aspx


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Saturday, April 12, 2014 12:34 AM

All replies

  • I got at test 1: Duration 121 cpu 62 reads 1452.

    Test 2:            Duration 166 cpu 78 reads 496.

    Not very conclusive.

    What is @@version?


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, April 11, 2014 8:44 PM
  • Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
    Dec 28 2012 20:23:12 
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)


    Friday, April 11, 2014 8:59 PM
  • There should be no difference.

    You would have to repeat the test you designed a few times to take readings and then reverse the order.

    BOL: " Benefits of Using Stored Procedures


            

    The following list describes some benefits of using procedures.

    Reduced server/client network traffic          

    The commands in a procedure are executed as a single batch of code. This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network. Without the code encapsulation provided by a procedure, every individual line of code would have to cross the network.

    Stronger security          

    Multiple users and client programs can perform operations on underlying database objects through a procedure, even if the users and programs do not have direct permissions on those underlying objects. The procedure controls what processes and activities are performed and protects the underlying database objects. This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers.

    The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user, or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands. For example, some actions such as TRUNCATE TABLE, do not have grantable permissions. To execute TRUNCATE TABLE, the user must have ALTER permissions on the specified table. Granting a user ALTER permissions on a table may not be ideal because the user will effectively have permissions well beyond the ability to truncate a table. By incorporating the TRUNCATE TABLE statement in a module and specifying that module execute as a user who has permissions to modify the table, you can extend the permissions to truncate the table to the user that you grant EXECUTE permissions on the module.

    When calling a procedure over the network, only the call to execute the procedure is visible. Therefore, malicious users cannot see table and database object names, embed Transact-SQL statements of their own, or search for critical data.

    Using procedure parameters helps guard against SQL injection attacks. Since parameter input is treated as a literal value and not as executable code, it is more difficult for an attacker to insert a command into the Transact-SQL statement(s) inside the procedure and compromise security.

    Procedures can be encrypted, helping to obfuscate the source code. For more information, see SQL Server Encryption.

    Reuse of code          

    The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions.

    Easier maintenance          

    When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.

    Improved performance          

    By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure.

    If there has been significant change to the tables or data referenced by the procedure, the precompiled plan may actually cause the procedure to perform slower. In this case, recompiling the procedure and forcing a new execution plan can improve performance. "

    LINK: http://technet.microsoft.com/en-us/library/ms190782.aspx


    Kalman Toth Database & OLAP Architect Free T-SQL Scripts
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

    Saturday, April 12, 2014 12:34 AM
  • >> I would like to know why if I created a temp table in my procedure the insert into it get slower than if I create that temp table inside my procedure. <<

    Who cares? In a declarative language like SQL, we do not use local variables or mimic 1950's scratch tapes. We use derived tables, CTEs and so forth.

    You need to read a book on declarative programming and quit thinking this way. Have you seen LISP? APL? Haskell? Your mindset is still in 1960's COBOL.  


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Saturday, April 12, 2014 2:44 AM