2

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?

  • Is SSIS an option? It's data pumps are designed specifically for this. Also, why are you inserting to a #temptable first? – Steve Mangiameli Jul 21 '16 at 20:13
  • @SteveMangiameli its azureDB :-) – Kin Shah Jul 21 '16 at 20:24
  • are there constraints you can disable? How about TABLOCK hint on the target table? Can you temporarily change the recovery model? – Kentaro Jul 22 '16 at 14:03
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

| improve this answer | |
  • You could try some of the non bcp suggestions here. Or here. As well as reading up on Trace Flag 610. – Aaron Jul 23 '16 at 21:24
  • Those are good suggestions but Azure databases do not allow DBCC trace flag commands – Alex Meyer Jul 23 '16 at 22:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.