Lesson Learned #158: Running a Insert Into …. Select when the source table is a Heap Table

by | Jan 26, 2021 | Technology | 0 comments

This article is contributed. See the original author and article here.

Today, I worked on a service request where I found an interesting situation running an Insert Into … Select command when the source table is a heap table. In this situation, I saw an additional sorting operation that depending on the number of rows of the source might take some resource and time. 


 


Background


 



  • I have a table called source that is a heap table using the following definition. 


 

CREATE TABLE [dbo].[Source](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](200) NULL
) ON [PRIMARY]

 



  • I have a table called target that has the following definition. 


 

CREATE TABLE [dbo].[Target](
	[ID] [int] NOT NULL,
	[NAME] [varchar](200) NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [tARGET1] ON [dbo].[Target]
(
	[NAME] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

 



  • I executed the following command to insert data from source to target table. 


 

TRUNCATE TABLE Target
INSERT INTO Target (id,name) select id,name from Source

 



  • I saw in the execution plan two additional sort operators. First for clustered index and second for the additional index that the table target has. 


 


Capture.PNG


 



  • If the source table has a clustered index in its definition the process will save around 6 seconds because it is not needed to sort the Source table. 


 


Capture2.PNG


 



  • In this situation, it is clear that depending on the target table definition SQL Server is performing additional operations that will take an additional time. For this reason, I performed the following changes reducing around 1 minute the execution for this workload:

    • Adding a PK (Clustered) in the Source table to have the same definition in terms of PK with target table. 

    • Removing the additional index in the Target table leaving only the PK. 

    • Run the INSERT INTO .. SELECT taking around 1 minute and 52 seconds. 

    • Creating the additional index in the Target table that took 26 seconds. Reducing more than 1 minute the execution of this query.




 Capture3.PNG


 


Capture4.PNG


 


Enjoy!!!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment