This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.
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.
- I have a table called target that has the following definition.
- I executed the following command to insert data from source to target table.
- 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!!!