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

by Contributed | Jan 26, 2021 | Technology

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. 

• 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. 

• 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.
  
  
  
  

Enjoy!!!

[EVENT] Microsoft Reactor: Remote Assist Deployment Best Practices – Feb 3, 8 AM PST

by Contributed | Jan 26, 2021 | Technology

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

Want to learn how to supercharge your organization and boost efficiency while reducing cost and your carbon footprint through the power of mixed reality? Join us for our first ever Microsoft Reactor event focused on Dynamics 365 Remote Assist Deployment Practices! 

Whether you’re an experienced Remote Assist user or someone brand new to the space and exploring Mixed Reality business applications for the first time, ALL are welcome!

Event details

Join R&D Solution Architect Payge Winfield as she shares her top tips on how you can leverage Dynamics 365 Remote Assist on HoloLens and mobile (iOS/Android) in your organization, deployment pitfalls to avoid and how you can unlock the power of mixed reality with ease!

Get your burning questions about mixed reality business applications answered in this interactive session and meet peers who are also exploring mixed reality solutions.

Date: February 3, 2021

Time: 08:00 AM – 09:00 AM (Pacific)

Format: Livestream

Topic: Mixed Reality

Register here today!

https://www.meetup.com/Microsoft-Reactor-Redmond/events/275710904/

Speaker details
Payge Winfield, Mixed Reality Solution Architect, Microsoft
Payge Winfield is a Mixed Reality Solution Architect. Payge’s day job involves assisting commercial customers in their deployment of HoloLens, Dynamics 365 Remote Assist, Guides, and custom-built mixed reality apps. She has successfully assisted many strategic customers in deploying mixed reality technology within their organizations and is passionate about sharing her insights, best practices, and common “gotchas” to enable others to also leverage the power of mixed reality. Outside of her day job, Payge is a voracious reader and is constantly learning new things, including security – which she strives to infuse into her mixed reality work.