Azure SQL SELECT of columns INTO #Temp table is executing very slowly. If I remove the INTO clause, it executes in 20 seconds instead on 10 minutes.

Kindly suggest, how do I optimize this query? Tempdb already has 4 data files. I also tried OPTION MAXDOP(2) but no luck. In the SELECT's I am already using WITH (NOLOCK).

Azure SQL SELECT of columns INTO #Temp table is executing very slowly. If I remove the INTO clause, it executes in 20 seconds instead on 10 minutes.

Kindly suggest, how do I optimize this query? Tempdb already has 4 data files. I also tried OPTION MAXDOP(2) but no luck. In the SELECT's I am already using WITH (NOLOCK).

• azure-sql-database
• query-optimization

• 1 Why do you need a temp table anyway? Either way we can't help you without more info: the full query, the table definitions, and please share the query plan via pastetheplan By the way, NOLOCK is not a go-faster switch, it has serious data integrity implications. – Charlieface Commented 21 hours ago
• Is it any better if you use INSERT INTO #Temp SELECT ...? See sqlshack/select-into-temp-table-statement-in-sql-server it has a section on performance. – Barmar Commented 20 hours ago
• Hey @Charlieface Thanks for your quick response, I will try and provide the details – Pankaj Rathod Commented 5 hours ago
• Hey @Barmar Thanks for your quick response, I will try and provide the details – Pankaj Rathod Commented 5 hours ago
• Can you provide some details about your table size? – Pritam Commented 4 hours ago

1 Answer 1

Glad to see that you fixed your issue, posting this as your answer which will help other community members who will face similar type of issue.

SELECT INTO statement creates temporary table with default setting without any schema defined for Original table. It will be easier syntax wise. select_into

But INSERT_INTO #temptable , will be more performant because here we are creating the temporary table with explicitly mentioning specific schema requirements, constraints, or indexes that are required for original table. Insert_into

Below here is the sample process which involves two steps to execute:

1. Create temporary table with similar schema and constraint as main table

               CREATE TABLE #TempEmp (ID INT  PRIMARY  KEY,
               Name  VARCHAR(10),
               Age INT,
               Department VARCHAR(50),
               Salary DECIMAL(10, 2)
               );
   

2. Insert data from main table to temporary table.

    INSERT INTO #TempEmp
    SELECT * FROM [dbo].[Employees];
   

Resouces for your reference:

Temporary Table Performance