Is there any way to optimize simple INSERT queries like the following?
INSERT master_table
SELECT * FROM increment
The execution plan is Table Scan (13%) -> Table Insert (87%)
What can I do to make it execute faster?
1 Answer
You leave little choice to scan the entire table as you want all the data, however a couple of things you can do.
- drop index, if you have any, on the
master_table - use
insert into master_table with(tablock)...
This will make the inserts into the target table as fast as possible and untilise parallel execution for reading the table to do the insert, a feature that was added in 2016.
3 Comments
SMor
You ignore the cost of adding the index(indexes) after the completion of the insert statement. And are you suggesting to drop the primary and all natural keys as well?
Stu
Of course there's a cost to recreate a dropped index, however in my experience the net-gain usually exceeds the cost of inserting into the table with the index present, particularly where data has to be sorted for a clustered index. As always, you have to test each to determine the best combination.
Karan T
Using TABLOCK seems good option in this situation. Utilizing the TABLOCK option will decrease concurrent access but immediately acquire a table-level lock on the target table. As long as it is ensured that only a single session will perform insert operations on the table, this approach avoids unnecessary row or page-level locks and prevents lock escalation.
SELECT *) and all rows (noWHEREclause); there's really very little (if anything) you can do to improve this. SQL Server will need to read the whole source table - all rows, all columns - and that's usually a full table scan (or a clustered index scan). Not much you can do about this, really.