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

Published Jan 26 2021 04:49 PM 1,160 Views

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!!!

%3CLINGO-SUB%20id%3D%22lingo-sub-2099926%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23158%3A%20Running%20a%20Insert%20Into%20....%20Select%20when%20the%20source%20table%20is%20a%20Heap%20Table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2099926%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20where%20I%20found%20an%20interesting%20situation%20running%20an%20Insert%20Into%20...%20Select%20command%20when%20the%20source%20table%20is%20a%20heap%20table.%20In%20this%20situation%2C%20I%20saw%20an%20additional%20sorting%20operation%20that%20depending%20on%20the%20number%20of%20rows%20of%20the%20source%20might%20take%20some%20resource%20and%20time.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EBackground%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%20have%20a%20table%20called%20source%20that%20is%20a%20heap%20table%20using%20the%20following%20definition.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20%5Bdbo%5D.%5BSource%5D(%0A%20%5BID%5D%20%5Bint%5D%20IDENTITY(1%2C1)%20NOT%20NULL%2C%0A%20%5BName%5D%20%5Bvarchar%5D(200)%20NULL%0A)%20ON%20%5BPRIMARY%5D%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%20have%20a%20table%20called%20target%20that%20has%20the%20following%20definition.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20TABLE%20%5Bdbo%5D.%5BTarget%5D(%0A%20%5BID%5D%20%5Bint%5D%20NOT%20NULL%2C%0A%20%5BNAME%5D%20%5Bvarchar%5D(200)%20NULL%2C%0APRIMARY%20KEY%20CLUSTERED%20%0A(%0A%20%5BID%5D%20ASC%0A)WITH%20(STATISTICS_NORECOMPUTE%20%3D%20OFF%2C%20IGNORE_DUP_KEY%20%3D%20OFF%2C%20OPTIMIZE_FOR_SEQUENTIAL_KEY%20%3D%20OFF)%20ON%20%5BPRIMARY%5D%0A)%20ON%20%5BPRIMARY%5D%0A%0ACREATE%20NONCLUSTERED%20INDEX%20%5BtARGET1%5D%20ON%20%5Bdbo%5D.%5BTarget%5D%0A(%0A%20%5BNAME%5D%20ASC%0A)WITH%20(STATISTICS_NORECOMPUTE%20%3D%20OFF%2C%20DROP_EXISTING%20%3D%20OFF%2C%20ONLINE%20%3D%20OFF%2C%20OPTIMIZE_FOR_SEQUENTIAL_KEY%20%3D%20OFF)%20ON%20%5BPRIMARY%5D%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%20executed%20the%20following%20command%20to%20insert%20data%20from%20source%20to%20target%20table.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ETRUNCATE%20TABLE%20Target%0AINSERT%20INTO%20Target%20(id%2Cname)%20select%20id%2Cname%20from%20Source%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EI%20saw%20in%20the%20execution%20plan%20two%20additional%20sort%20operators.%20First%20for%20clustered%20index%20and%20second%20for%20the%20additional%20index%20that%20the%20table%20target%20has.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249684i4742C2CFAC6FF807%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture.PNG%22%20alt%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIf%20the%20source%20table%20has%20a%20clustered%20index%20in%20its%20definition%20the%20process%20will%20save%20around%206%20seconds%20because%20it%20is%20not%20needed%20to%20sort%20the%20Source%20table.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture2.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249685iEF26ECE29FD5B4C1%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture2.PNG%22%20alt%3D%22Capture2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EIn%20this%20situation%2C%20it%20is%20clear%20that%20depending%20on%20the%20target%20table%20definition%20SQL%20Server%20is%20performing%20additional%20operations%20that%20will%20take%20an%20additional%20time.%20For%20this%20reason%2C%20I%20performed%20the%20following%20changes%20reducing%20around%201%20minute%20the%20execution%20for%20this%20workload%3A%3CUL%3E%0A%3CLI%3EAdding%20a%20PK%20(Clustered)%20in%20the%20Source%20table%20to%20have%20the%20same%20definition%20in%20terms%20of%20PK%20with%20target%20table.%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3ERemoving%20the%20additional%20index%20in%20the%20Target%20table%20leaving%20only%20the%20PK.%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3ERun%20the%20INSERT%20INTO%20..%20SELECT%20taking%20around%201%20minute%20and%2052%20seconds.%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3ECreating%20the%20additional%20index%20in%20the%20Target%20table%20that%20took%2026%20seconds.%20Reducing%20more%20than%201%20minute%20the%20execution%20of%20this%20query.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%E2%80%83%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture3.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249692i414DB9D45B546C8C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture3.PNG%22%20alt%3D%22Capture3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture4.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F249693iCAE0FCA71ACE9145%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture4.PNG%22%20alt%3D%22Capture4.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2099926%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20worked%20on%20a%20service%20request%20where%20I%20found%20an%20interesting%20situation%20running%20an%20Insert%20Into%20...%20Select%20command%20when%20the%20source%20table%20is%20a%20heap%20table.%20In%20this%20situation%2C%20I%20saw%20an%20additional%20sorting%20operation%20that%20depending%20on%20the%20number%20of%20rows%20of%20the%20source%20might%20take%20some%20resource%20and%20time.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Jan 26 2021 04:53 PM
Updated by: