Fastest method for inserting large number of records from query between tables

Question

I have a query that returns 14 million results that I need to store in a table, the SELECT query itself returns in a little over 2 minutes but INSERT statement takes well over 30 minutes (which is unacceptable). We are running this in an Azure Database currently set as a Premium tier database, and I need to keep this process all contained within SQL (no exporting to file etc).

Sample Query:

SELECT
v.Param1,
v.Param2,
lt.Param3,
lt.Param4
INTO #tempTable
FROM <View> v
  join LargeTable lt
    on lt.nVarCharCol = v.nVarCharCol

INSERT INTO DestTable SELECT Para1, Param2, Param3, Param4 FROM #tempTable

Things I have tried so far:

• Turned off indexes on DestTable
• Batching inserts
• Creating indexes on temp tables

What is the fastest/best way to insert these query records into a table?

Any recommendations to speed this process up?

Answer 1

After trying numerous solutions, this is the best I could come up with

DECLARE @BatchSize INT = 50000
DECLARE @page INT = 0

SELECT
 IDENTITY(int,1,1) as id
 v.Param1,
 v.Param2,
 lt.Param3,
 lt.Param4
INTO #tempTable
FROM <View> v
  join LargeTable lt
   on lt.nVarCharCol = v.nVarCharCol

WHILE(1=1)
BEGIN
 BEGIN TRANSACTION
  INSERT INTO DestTable SELECT Para1, Param2, Param3, Param4 FROM #tempTable
  WHERE id > @BatchSize * @Page AND id <= @BatchSize * (@Page + 1)
 IF @@ROWCOUNT = 0
  BREAK;
 SET @Page = @Page + 1
 COMMIT TRANSACTION
END

Also be sure to keep in mind that regardless of how fast your query or T-SQL statement is Azure SQL Databases have an artificial I/O ceiling called DTUs. I found that there was a substantial difference in performance between the different performance levels with the exact same T-SQL statement.

More information about DTUs can be found here: SQL Database options and performance