SELECT INTO vs INSERT INTO

  • npranj

    SSCrazy

    Points: 2323

    Hi,

    I am trying to create a temporary table. It will contain approx 25 million rows to start with and will increase in future.

    I tried

    Option1: SELECT col1 into #tempT from table

    and

    Option2: create table #tempT (col1 int)

    insert into #tempT

    select col1 from table

    While doing both I ran the following to get locking information.

    SELECT resource_type,

    (CASE WHEN resource_type = 'OBJECT'

    THEN object_name(resource_associated_entity_id)

    WHEN resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN 'N/A'

    WHEN resource_type IN ('KEY', 'PAGE', 'RID')

    THEN ( SELECT object_name(object_id)

    FROM sys.partitions WHERE hobt_id=resource_associated_entity_id )

    ELSE 'Undefined' END) AS requested_object_name,

    request_mode AS lock_type, request_status,

    request_owner_id AS transaction_id

    FROM sys.dm_tran_locks

    where request_session_id =52

    Question:

    1. I have read that SELECT INTO creates exclusive lock on objects in the system catalog. In fact, creation of any object would do that, howwever when SELECT INTO runs for longer then the catlog locking will be longer and at that point in time if you try to query system catalog then you might run into blocking issues.

    When i ran the query to get the locking information I see only schema locks on the table from which i am trying to read the data and possibly the new temp table that was being created. This happened for both SELECT INTO and INSERT INTO.

    Can someone help me uunderstand -

    1. How INSERT INTO is better as compared to SELECT ITNO when there are huge datasets expected.

    2. Would this SELECT INTO also hamper the rebuild index and other db activities if the catlog is exclusively locked.

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    npranj (2/28/2013)


    Hi,

    I am trying to create a temporary table. It will contain approx 25 million rows to start with and will increase in future.

    I tried

    Option1: SELECT col1 into #tempT from table

    and

    Option2: create table #tempT (col1 int)

    insert into #tempT

    select col1 from table

    While doing both I ran the following to get locking information.

    SELECT resource_type,

    (CASE WHEN resource_type = 'OBJECT'

    THEN object_name(resource_associated_entity_id)

    WHEN resource_type IN ('DATABASE', 'FILE', 'METADATA') THEN 'N/A'

    WHEN resource_type IN ('KEY', 'PAGE', 'RID')

    THEN ( SELECT object_name(object_id)

    FROM sys.partitions WHERE hobt_id=resource_associated_entity_id )

    ELSE 'Undefined' END) AS requested_object_name,

    request_mode AS lock_type, request_status,

    request_owner_id AS transaction_id

    FROM sys.dm_tran_locks

    where request_session_id =52

    Question:

    1. I have read that SELECT INTO creates exclusive lock on objects in the system catalog. In fact, creation of any object would do that, howwever when SELECT INTO runs for longer then the catlog locking will be longer and at that point in time if you try to query system catalog then you might run into blocking issues.

    When i ran the query to get the locking information I see only schema locks on the table from which i am trying to read the data and possibly the new temp table that was being created. This happened for both SELECT INTO and INSERT INTO.

    Can someone help me uunderstand -

    1. How INSERT INTO is better as compared to SELECT ITNO when there are huge datasets expected.

    Both can be minimally logged if other system conditions exist that allow each to achieve it and proper coding techniques are applied. You'll need to test both methods in your environment with your data and see which one does best.

    2. Would this SELECT INTO also hamper the rebuild index and other db activities if the catlog is exclusively locked.

    Not if the SELECT INTO is targeting a true temporary table, e.g. #tempTable.

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • npranj

    SSCrazy

    Points: 2323

    In the test environment, INSERT INTO takes more time as compared to SELECT INTO.

    There are 20 million records getting inserted. In future there could be more. So I am more worried on the future perspective.

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    When you use INSERT INTO are you adhering to the requirements to achieve a minimally logged operation?

    From Books Online INSERT (Transact-SQL):

    Using INSERT INTO…SELECT to Bulk Import Data with Minimal Logging

    You can use INSERT INTO <target_table> SELECT <columns> FROM <source_table> to efficiently transfer a large number of rows from one table, such as a staging table, to another table with minimal logging. Minimal logging can improve the performance of the statement and reduce the possibility of the operation filling the available transaction log space during the transaction.

    Minimal logging for this statement has the following requirements:

    - The recovery model of the database is set to simple or bulk-logged. - tempdb qualifies

    - The target table is empty or is a nonempty heap. - I think this is your case

    - The target table is not used in replication. - temp tables would qualify

    - The TABLOCK hint is specified for the target table. - did you provide the hint?

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply