February 28, 2013 at 9:18 pm
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.
February 28, 2013 at 11:11 pm
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
February 28, 2013 at 11:35 pm
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.
March 1, 2013 at 7:05 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply