Apr 13, 2014

Fastest or most efficient way to insert data in sql server

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 table has not any clustered index:

INSERT INTO tblDestination WITH(TABLOCK)
SELECT * FROM tblSource WITH(NOLOCK READUNCOMMITTED)

2. If 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 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: Second option is better if there are multiple insert statements are executing on same tblDestination table.

4. If 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 table has clustered index as well has 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


No comments:

Post a Comment