Sometimes we need to insert large or big amount of data from one table to another table using INSERT SELECT statement. For example:
INSERT INTO tblDestination
SELECT * FROM tblSource
Here we will discuss how to optimize (or write most efficient way) above insert statement:
1. If a table has not any clustered index:
INSERT INTO tblDestination WITH(TABLOCK)
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)
2. If a table has not any clustered index but has other non-clustered indexes:
DBCC TRACEON (610)
GO
INSERT INTO tblDestination WITH(TABLOCK)
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)
DBCC TRACEOFF (610)
GO
Note: It is dependent on query execution plan.
3. If a table has only clustered index and destination table is empty:
INSERT INTO tblDestination WITH(TABLOCK)
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)
Or
DBCC TRACEON (610)
GO
INSERT INTO tblDestination
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)
DBCC TRACEOFF (610);
GO
Note: the Second option is better if there are multiple insert statements are executing on same tblDestination table.
4. If a table has only clustered index and destination table is not empty:
DBCC TRACEON (610)
GO
INSERT INTO tblDestination
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)
DBCC TRACEOFF (610);
GO
5. If a table has clustered index as well as non-clustered indexes:
DBCC TRACEON (610)
GO
INSERT INTO tblDestination
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)
DBCC TRACEOFF (610);
GO
Or
DBCC TRACEON (610)
GO
INSERT INTO tblDestination WITH(TABLOCK)
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)
DBCC TRACEOFF (610);
GO
Note: It is dependent on query execution plan.Also, the first option is better if there are multiple insert statements are executing on same tblDestination table.
Indexes best practices in SQL server: Where to start creating indexes?
SQL Server query optimization tips: Tuning best practices with examples
Write SQL queries in set-based approach SQL server
SQL server best practices for stored procedure
What is Adhoc query in SQL server: Cached Query Plan
Indexes best practices in SQL server: Where to start creating indexes?
SQL Server query optimization tips: Tuning best practices with examples
Write SQL queries in set-based approach SQL server
SQL server best practices for stored procedure
What is Adhoc query in SQL server: Cached Query Plan
No comments:
Post a Comment